The final solution will depend on the 'shape' of your data, but I'd use the 'built' in quick table calculations in Tableau. You can find this in options by clicking on the measure you want to show the difference for
to get your YoY, by Quarter to calculate correctly you'll need to go into the 'advanced' set up (this can be found by selecting 'Edit Table Calculation...'). By default Tableau will do QoQ (so Q4 vs Q3, Q3 vs Q2...etc.)
I've set the attached up as per the below image
So I want to address Year (this is what I want the YoY over), and then partition on Quarter (as you want to restart the YoY calc every quarter). For this to work you'll need to have year in the Viz (btw this doesn't necessarily mean displayed as there are tricks around this), so the Table Calc can calculate over both year and quarter.
There are times when you do want to 'materialise' each time-slice of a measure into a separate field, but in the main I'd use the built in Table Calcs a they dynamically run over the entire level of the Viz. In your method you'd need to create a field for every quater/year and then the variance.
I hope this makes sense, and helps your situation, but please post back if not
Table Calcs Difference QonQ.twbx 348.5 KB
Thanks Simon the solution was correct. As I understand from above, for YoY weekly comparisons I can pull in the Quarters field along with the year field and do a WoW comparison right ?
Glad it all made sense! Yes for WoW it's exactly the same logic, except you need to have Week (as a datepart...when you look in the date pill you'll see 2 lots of 'date slices'. The top set are Dateparts, so q1, q2...or w1, w2...and the lower ones are datetrunc...so q1 2011, q2 2011...w1 2011, w2 2011...). So you need the datepart ones. I think, from memory, week number is in the 'other' submenu,
I'm not going to be at my laptop today, so can't post you a workbook but hopefully that makes sense
Sent from my iPhone
I have a new issue now.
I am trying to color code the 4 quarters with the below logic :
IF ([Actual Gross Sales] <= 0 THEN "Red" ELSE "Green" END.
For some reason it is not coloring correctly.I have used the above method as you suggested.
Please help. Additionally will also want to alias the headers.
apologies for not getting back sooner...on my holiday and has taken a while to find some good WiFi!!
So your formula here
IF ([Actual Gross Sales] <= 0 THEN "Red" ELSE "Green" END
is doing 2 things you don't want it to! Firstly it's a row level formula. This means it's being assessed at each row of your data and not at an aggregated level. It's actaully doing this, so not giving a single colour per quarter
The other problem, and I assume this is what you want, is that it's not looking at the variance, but the actual value.
There are, as always with Tableau, a few ways to do this...lets start with the simplest.
The simplest way to get this colouring is to drag a copy of your calculation onto the colour shelf (if you hold CTRL and drag the variance calculation it will drag a duplicate), and then format the colour as per the below image. Here I've asked for only 2 colours, and set the centre to zero.
The other way, and in line with your calculated field method...If you double click in our variance measure, you'll see that Tableau actually created the formula for this variance
(ZN(SUM([Sales])) - LOOKUP(ZN(SUM([Sales])), -1)) / ABS(LOOKUP(ZN(SUM([Sales])), -1))
Looks a bit scary...but is just the Sales-Sales [one back in the partition]/Sales [one back in the partition]
or This Q Sales - Last Q Sales/Last Q Sales
You can then use this formula in your colour calculated field
IF (ZN(SUM([Sales])) - LOOKUP(ZN(SUM([Sales])), -1)) / ABS(LOOKUP(ZN(SUM([Sales])), -1))<= 0 THEN "Red" ELSE "Green" END
and then drag this onto your colour shelf. In the attached I've given both solutions.
On the Alias' question...it depends what you want to call them. You have some 'default' control over display...if you right click on on of the Quarter headers, and select 'format', you can change the display to 1 of 4 different formats
If you want full control you can create a calculated field, like I've done below
'Our Very Own Q' + str(DATEPART('quarter', [Order Date])) + 'Display'
You can then hide the quarter header. Right Click on it, and untick 'show header'.
Hope this helps and all makes sense.
Table Calcs Difference QonQ.twbx 347.0 KB
Thanks for the detailed reply. Very useful.
On running a test on the values I see that the values are not matching for some of the rows. I have used your method of percentage difference calculation. I also have a country dimension and every country has its own percentages. Is this split on country causing the data to not match ? So I have Country | Q1 | Q2 | Q3 | Q4 as the table
Please advise on what may be wrong.
Yes adding extra dimensions 'might' change the calculation. Table Calculations are 'relative' to the dimensions in your Viz...and how they are arranged.
For a great explanation on how they work...etc. here is a link to a brilliant video on the subject
This is what the default 'Compute Using'/'Addressing' are all about...you'll see in the 'Compute Using' options, Table Down, Pane Down...these run the calculations depending on the arrangement of dimension (pills) in your Viz. By using the advanced option, we can specify exactly how we want the table calculation to calculate. As I don't know the arrangement of your final Viz, I went down this route (personally I prefer this way, as if I change the arrangement of pills [say move a pill from Columns to Rows, or change the order, they still work the way I set them up...but this is a personal view!)
So with the country dimension added my Table Calc is now set up as follows
Now with my current arrangement of pills I could have done 'Pane Down' and got the same result. however had I switched Year and Country round, the 'pane' down would no longer work...whereas by specifying it as I have in the above image (Telling Tableau exactly which dimensions I was to be addressing and which partitioning) the var % is correct however I arrange my Viz.
Hope this makes sense, but definitely worth watching the Video from Jonathan who explains thing much better than me!!!
As well as an updated workbook with country in, I've also attached an Excel document so you can see how each is calculated.
Thanks Simon.Very useful insights.
I have mocked the dataset I have as attached. The State in the sample represents the country in my report, I verify and see many of the displayed percentages have wrong values.
Please advise on the same file.
Thanks Simon Runc
I've had a quick look at this and seems to be calculating correctly to me...can you give me an example of an incorrect figure. If the 'incorrect' figures are the slight differences are the ones such as the below
where if you do 1721/1320-1 you get 32.30%...this is just due to the display being to zero decimal places, but the original figures are actually 1721.47 and 1319.75 and Tableau is doing the calculation over the 'full' numbers.
If it's the Quarter on Quarter you want, you don't have Quarter in the view (vizLoD as it's called). Table Calculations are relative to the VizLoD so if you don't have Quarter, it can't calculate on this level...say you wanted Week on Week, how would Tableau know you want Week this time and not Quarter!
I've attached a couple of extra worksheets. One showing the variance by Quarter, and another (using your original view) but adding a quick-filter on Quarter, so by selecting a quarter the YoY calc will be based on this quarter.
Hope this is what you mean, but if not let me know. If you can let me know a figure that is 'incorrect' and what you expect it to be I'll (hopefully!!) be able to see why it's not doing what you want.