8 Replies Latest reply on Aug 22, 2016 5:47 AM by Andrew Watson

    Calculations on cumulative data

    Artur Pogorzelski

      I'm still quite new to tableau and I'm working on cumulative data which isn't ideal but I have no other option.

       

      I need to make a dashboard to show how our sales guys are doing against their targets. Their targets are quarterly, so they start from scratch against their target every quarter. This is what the targets look like:

      Targets qtd.PNG

      and this is what the sales figures look like:

      Net sales cumulative.PNG

      Is there any way I can make the sales start from 0 every time a new quarter starts?

       

      I've tried this formula but It doesn't work:

       

      if [Quarter]="Q1" then [Net New]

      elseif [Quarter]="Q2" then [Net New]-[Week13 Net New]

      elseif [Quarter]="Q3" then [Net New]-[Week26 Net New]

      end

       

      Would be grateful if someone could help!

        • 1. Re: Calculations on cumulative data
          David Li

          Hi Artur! When you say that you're working on "cumulative data", do you mean that the value stored in your data source for each week includes the sum of all previous weeks? And do you have a packaged workbook you can share with us?

           

          Off the top of my head, I think the best way is to back into the non-cumulative values. If you can't do this before the data gets into Tableau, you can do it within Tableau by creating a calculated field like this:

           

          SUM(Sales)-LOOKUP(SUM(Sales), -1)
          

           

          As long as your level of detail is down to weeks and you compute along weeks, this should give you the weekly values. Then, you can just do a rolling sum of this new calculated field that resets every quarter.

           

          This will also help you with other calculations where you need the weekly values.

          1 of 1 people found this helpful
          • 2. Re: Calculations on cumulative data
            khalid norat

            Can you share a larger screen shot so we can see what fields you are using on your graph

             

            also the definitions of any calculated fields like [week 13 net new]

             

            you should be using

             

            Max(if quarter = Q1 then [netNew] end)

             

            If you can share the required details

            or even better a sample workbook. Loaded with sample data if need be.

            I can help you achieve the desired result

            • 3. Re: Calculations on cumulative data
              Andrew Watson

              You should be able to use the RUNNING_SUM advanced options and tell it restart every Quarter.

              • 4. Re: Calculations on cumulative data
                David Li

                I'm not sure if RUNNING_SUM will work since his source data is already essentially a cumulative running sum. But maybe he misspoke and meant "aggregated" instead of "cumulative". Hard to say without seeing the data.

                • 5. Re: Calculations on cumulative data
                  Andrew Watson

                  Yes, quite right David. Artur, please help us to help you by posting some of your data.

                  • 6. Re: Calculations on cumulative data
                    Andrew Watson

                    I've mocked something up to try and do this. First step is to deconstruct the cumulative values back to the daily to allow a running_sum to be done.

                     

                    Create a calculated field to get the previous value, LOOKUP(SUM([Amount]),-1)

                     

                    Next we want to minus that value from the current value to calculate the contribution from the day only:

                     

                    IF FIRST() = 0 THEN SUM([Amount]) ELSE SUM([Amount])-[PreviousValue] END

                     

                    Note the FIRST() = 0 - this is saying if it's the very first value - i.e. not yet cumulative - then keep that value as it is, otherwise take the current value and minus the previous.

                     

                    Next calculated field is the Running_sum on the above: RUNNING_SUM([AboveCalc]). Tableau 10 screenshot below telling it to restart (In my mock up data I had day to month rather than week to qtr):

                     

                    Tableau 10 workbook of this mock up attached.

                    1 of 1 people found this helpful
                    • 7. Re: Calculations on cumulative data
                      Artur Pogorzelski

                      Hi David,

                       

                      Thanks for your reply. Yes, each week is a running sum of the weeks before it. I've used the formula you suggested to strip the data back to distinct weekly. How would I now go about making it cumulative but restart from 0 every quarter?

                       

                      Unfortunately I can't open your workbook because I'm still running an older version of Tableau.

                      • 8. Re: Calculations on cumulative data
                        Andrew Watson

                        Create a RUNNING_SUM of your weekly value. Then you need to go into the Advanced settings of the Compute Using. It looks different to the screenshot above in older versions of Tableau. I'm trying to go from memory, you want to put the Quarter and Week fields to the right (I think it's the 'addressing' box) of your Advanced options, leave 'At the level' as deepest and 'Restarting every' Quarter (your Quarter field).

                        1 of 1 people found this helpful