Please see attached answer.
I have two solutions, the second one is quite involved. The first is a bit simpler. The trick here is to use a Table Calculation formula that checks for a True/False condition of whether the bar is greater than or less than previous. And the table calculation should be set to calculate "Pane Down".
1. On sheet one - I used color to depict the difference from previous. Since you cant "overload" color on the bars, this replaces your existing color by dimension.
2. Sheet two is a bit more involved, but it allows you to preserve your existing coloration. It uses table calculations just like #1, but incorporates dual axis charts, and shapes with colors to represent if the bar was up or down compared to previous.
Firstly many thanks for your two solutions, the second solution would work for what I'm looking for, but the calculation you have done is not quite right for what I need.
I only want the calculation to show the shape on the 'Last Week' bar and no shapes on the '4 Week Avg.' and '12 Week Avg.' bars.
So the condition should be that the 'last week' looks at the '4 Week Avg.' and '12 Week Avg.' and if the 'last weeks' figure is better than both the '4 Week Avg.' and '12 Week Avg.' then the green up arrow should display, else the red arrow should display.
I've tried to edit and customise your calculated fields for this, but so far no luck.
Is what I've suggested in this reply possible in Tableau.
Once again many thanks in advance.
Ah.. I will look into it later today. Meanwhile though, seems to me that you are aggregating this data before you pull it into tableau. Is there a reason for that? You might be better off getting the individual rows, and using tableau to calculate the 4 week, 12 week averages using a table calc. The reason I propose this is because then you have better control over your data and visualization, giving you a different way of looking at this data.
My response earlier was to specifically address a particular viz challenge you have. However, on looking at what your data intends to convey, I am wondering if you would consider ways to "answer the intended question".
For example something like this... gives a week over week trend of orders, and where the line is black it is basically over the 4 wk, and 12 wk avg. This was done using the sample superstore data.
1 of 1 people found this helpful
Back to your original question -
Use this formula in place of the existing one: iif(FIRST() = 0, LOOKUP(SUM([Sessions]), FIRST()) = WINDOW_MAX(SUM([Sessions])), null)
Attached is the solution.
I wrote the final answer using just the "Sessions" measure. But you can apply same logic to the other measures. The reason I used Sessions was that it was the only one that gave me both positive and negative values so I could test it better. As you will see in the answer, when you apply this - you will get a shape and a color for the null values as well. All you will need to do is right click on the value in the legend, and just "hide" it.
Please note that this solution works only if the "Last week" bar is the first one on your viz.
Thank you! This is exactly what i need.
Regarding your reply on aggregating the data outside of tableau, I did this as I could not aggregate the data inside Tableau for the specific periods i needed (I am still very new to Tableau at the moment).
If i was to aggregate the data in Tableau, would it be possible for me to have the Viz i have above (the bar chart).