
1. Re: Complicated Calculation in Tableau
Simon Runc Jan 13, 2017 2:02 AM (in response to shruti.c)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 cellbased 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.

Running Average.twbx 34.0 KB


2. Re: Complicated Calculation in Tableau
shruti.c Jan 13, 2017 10:39 AM (in response to Simon Runc)Thank you so much.
Your method worked beautifully.