2 Replies Latest reply on Jan 13, 2017 10:39 AM by shruti.c

    Complicated Calculation in Tableau

    shruti.c

      I have the following data:

       

                          Group1     Group2        Group3

      Date1:            505            968            29964

      Date2:            504           1019           29516

       

      Date3:             515           984             28982

      Date4:             487          1081           29430

       

       

      There can be more dates and groups.

       

      So the calculation is:

      1) Find the average of Date1 and Date2 for each Groups.

      2) Calculate the running average for Date3 and Date4 for each Groups

      3)Subtract the value in Step-2 for each Group with the average in Step1 for each Group.

      4) Subtract value in Step 3 for Group 1 and Group2 with Group3. This is the final result for each Group which needs to be shown as a chart if possible.

       

      How do I proceed?

       

      The original Data looks like this:

      Date1  Group1     505

      Date1 Group2       968

      and so forth.

       

      This is for a client and need to be done asap. The excel calculation is uploaded.

        • 1. Re: Complicated Calculation in Tableau
          Simon Runc

          hi Shruti,

           

          So this kind of "Excel" (cell based) calculations are quite tricky in Tableau. In short Tableau works like a database (using set theory) and Excel is a cell-based technology. This means the in Excel we can string cells together to create our calculations, whereas in Tableau calculations are operated over the whole column. This, btw, is why Tableau can handle 10s Millions of rows and Excel struggles with a few hundred thousand!

           

          However most things are still possible!...and I've managed to recreate your calculations. First thing to say is that we need Date in the VizLoD, as the Running Average needs this to calculate how we want it.

           

          So first I created a INT version of your date (just to ensure ordering).

           

          I then created

          [Average Date < 4 - LoD]

          {FIXED [Group]: AVG(IIF([Date INT]<=4,[Value],NULL))}

           

          This gives me the average for each group, where date < date 4...and as an LoD this is applied to every row (at the Group Level)

           

          Next I created the Running Sum (where date > 5)

          [Running Average - Date > 5]

          RUNNING_AVG(SUM(IIF([Date INT]>=5,[Value],NULL)))

           

          I set the compute using to [Date INT]

           

          Then we can do the variance

          [Running Average - Date > 5 - Var]

          [Running Average - Date > 5]/SUM([Average Date < 4 - LoD])-1

           

          and then finally we need to create this var vs Group 3 (as group 3 is the last group, numerically, I can use LAST() to pick this up in a LOOKUP. We also now have a nested Table Calculation, and we need to set the compute using differently for each one.

           

          [Running Average Var vs Group 3]

          [Running Average - Date > 5 - Var]

          -

          LOOKUP([Running Average - Date > 5 - Var],LAST())

           

          where

          [Running Average - Date > 5] is set to Compute Using Date INT (as before)

           

          but the Var to Group 3 is running table across (or Group if your arrangement of pills is different)

           

           

           

          Hope that helps.

          • 2. Re: Complicated Calculation in Tableau
            shruti.c

            Thank you so much.

             

            Your method worked beautifully.