Tableau: Trend chart Previous Years in Current Year

Use the following formula to display Current and Previous Years trend lines while having a Relative Date filter:

Relative Date: DATEADD(‘year’,DATEDIFF(‘year’,[Order date], today()), [Order date])

This formula will add 4 years to orders made in 2010 (if current year is 2014), 3 years to orders from 2011, 2 years for 2012, and 1 year for 2013. Therefore the year is 2014 for all rows, and the relative date filter is applied across all years of orders.
Have the new calculated field Relative Date as column and use existing Order Date as color distinction.

Filed under Tableau | Tagged , , , | Leave a comment

Tableau: toggle between charts

If you need to toggle between charts you have to:
- create a sheet for each chart
- create 1 string list parameter, which you place on each sheet, display parameter
- create a calculated field referencing the parameter
- add the calculated field as a filter on each sheet (and verify that only the value selected in the parameter on the sheet is selected)
- create dashboard and place all sheets floating and aligned over each other, display the parameter window and select a value, you’ll notice that the charts will toggle.

Filed under Tableau | Tagged , , , | Leave a comment

Tableau: Order of Execution


Order of Execution in Tableau:

Computed in the database:
1. Context filters
2. Top N and conditional filters
3. Regular quick filters

Computed in Tableau:
4. Table calculations are computed
5. Table calculation filters are applied
6. Reference lines

This means that for a Top N chart that needs to display the Top 10 Sales per State, Average Sales of Top 10 States and the Average Total Sales all States, you have to make sure that the average all states needs to be computed before the filter is applied, you have to use the Window_AVG and add it to dual axis as line. The Top N condition needs to be applied after the Window_Avg calculation. The Windows_AVG is calculated in step 4, so the TOP N needs to be in step 5 or 6. The ‘Normal’ way of calculating TOP N is on database and cannot be used that leaves a table calculation filter. Add the Sales measure to the filter, add a quick table calculation Rank, and then modify this to run along State and keep the sorting in Descending order.

In case you need a filled area chart on Percent of Sales with possible filter option, this execution path means you need to make sure you create a table calculation on your filter dimension else the % of Total will be 100% instead of the correct share of total. You could use e.g. a calculated field like Region select: lookup(Max([Region]),0) and then filter on this new field.

Filed under Tableau | Tagged , , , | Leave a comment

Tableau: Filter with context

I had to apply a filter on a Top N chart; select a Region, then display Top N States. When I added Region to the filter section. it showed me from the Top N states overall, only the states in the selected region. However I needed to reset the filter top Top N within the selected region.

Notice that Top N filters are applied before regular quick filters, so this just shows of the top N states, which are in the east region. Notice as well that the Region pill is colored in dimensional blue.

Solution: Click the drop down on the region pill on the filter shelf and select ‘add to context’. Context filters are applied before Top N filters, so this then shows the top N states within the context of the selected region. Notice that the Region pill is now colored in grey.

Filed under Tableau | Tagged , , , | Leave a comment

Tableau: Waterfall chart from Budget to Actual

It has been a while since I posted anything. Currently I am exploring Tableau Software, as I encounter some challenges as well, I decided to post those in a separate section.

This will be the first post.

I did not find a good example on how to build a waterfall chart. I could only find partially constructed ones. So from 0 building up to a total.

But not starting eg with total budget and then go to total Actual.

I have come up with the following solution, it might not be the best yet (I only started a week ago with Tableau at all), but it is something. The trick is: use 3 charts, 1 for total budget, 1 for the waterfall, and 1 for total actual, use a formula for total budget in window to start the running total from: Total(sum([Budget Profit])) + running_sum(sum([Profit])-sum([Budget Profit]))


See for dashboard in Tableau Public: view dashboard

Filed under Tableau | Tagged , | Leave a comment

Do you know these tricks?

During the last couple of months I have discovered some usefull tricks, which in turn are too small for a post on its own, so I have combined them into this one.

Formula for calculating the maximum scale for chart
max scale: =ROUNDUP(Ceiling(MAX(RANGE)),0)*(1&REPT(0,(Len(Ceiling(MAX((RANGE)),0))-1))

wrap legend series
Set the format of the series name cell in Excel to wrap and use the carridge return to set the breakpoint ( alt enter ).

wrap label at specific point
Unfortunately the formula Char(10) is not supported. But with using the html format setting you can use
to set the break where you want in a formula concatenation. Just remember with this setting, you have to add the font formatting and color codings as well, as they are ignored otherwise. And do NOT FORGET to deselect the wrap option in the behaviour tab.

scorecard sorts rows, even when disabled, after reload button
If you have deselected the option enable sort in the scorecard, the user is not able to sort in the scorecard even though he clicks on the column headers. However when the user clicks the reload button, the dashboard is reset but the scorecard “retains” the sorted column and the whole scorecard is messed up. The only way to prevent this behaviour is by placing a transparent push button over the headers.

mouse over displays different decimals for different series
When you format the values in the spreadsheet with different decimals and keep the format text setting in the properties tab of the chart on general, the number is formatted as in the spreadsheet.
Note: unfortunately Excel and thus Xcelsius will shorten any number ending on 0. Hence the value 1.50 will be shown as 1.5 eventhough the formula specifies 2 decimals , like: =round(A1;2) will result into 1.5 if A1 is 1.5000 while 1.4999 will be 1.50.

scale y-axes displays different decimals depending on metrics
Following situation: combo box with selections on different metrics. Metric A has to be displayed with 0 decimals, metric B has 2 decimals. One chart displays the selected metric. The mouseover is like the above trick. The scale will follow formatting of the manual scale setting (link min and max to spreadsheet) and format vertical axes to general on text property tab.
Note: unfortunately Excel and thus Xcelsius will shorten any number ending on 0. Hence the value 1.50 will be shown as 1.5 eventhough the formula specifies 2 decimals , like: =round(A1;2) will result into 1.5 if A1 is 1.5000 while 1.4999 will be 1.50.

Filed under Components, Design, Excel | Tagged , , , , , | 1 Comment