9 Replies Latest reply on Oct 16, 2015 2:50 AM by Simon Runc

    Comparison Calculation - Performance

    buildstar

      Hi All,

       

      I am trying to compare QoQ values with prior year and display the same in my reports

       

      Current Year QTD - [QTD]

       

      ZN(SUM(IF [Day] >= DATETRUNC('quarter',now()) AND [Day] < now() THEN Sales] END))

       

      Previous Year QTD - [Prior QTD]

       

      ZN(SUM(IF [Day] >= DATETRUNC('quarter',DATEADD('year',-1,NOW())) AND [Day] < DATEADD('year',-1,NOW()) THEN [Sales]  END))

       

      I am also doing

       

      ([QTD] - [Prior QTD ]) / [Prior QTD]

       

      So the problem I face is when I do this it goes by now() datetime. I want to display all the four quarter values irrespective of the current datetime.

       

      Is this the correct calculation to achieve the QoQ comparions and if so how to display all 4 quarters.

       

      Thanks

        • 1. Re: Comparison Calculation - Performance
          Simon Runc

          hi Gallop,

           

          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 forQuick TC.png

          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

          Table Calc Set Up.PNG

          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

          • 2. Re: Comparison Calculation - Performance
            buildstar

            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 ?

            • 3. Re: Comparison Calculation - Performance
              Simon Runc

              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

              • 4. Re: Comparison Calculation - Performance
                buildstar

                Simon,

                 

                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.

                 

                Untitled.png

                 

                Please help. Additionally will also want to alias the headers.

                 

                Thanks

                • 5. Re: Comparison Calculation - Performance
                  Simon Runc

                  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


                  Row LEv.PNG

                  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.


                  2 tone colour.PNG


                  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


                  formatting Qs.png


                  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.







                  1 of 1 people found this helpful
                  • 6. Re: Comparison Calculation - Performance
                    buildstar

                    Hi Simon,

                     

                    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.

                    • 7. Re: Comparison Calculation - Performance
                      Simon Runc

                      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

                       

                      TDT with Jonathan Drummey - July 10, 2014

                       

                      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

                      TC Set Up with Country.PNG

                      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.

                      • 8. Re: Comparison Calculation - Performance
                        buildstar

                        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

                        • 9. Re: Comparison Calculation - Performance
                          Simon Runc

                          hi Gallop,

                           

                          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

                          Calc Error.png

                           

                          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.