
1. Re: Help with calculations
swaroop.gantela Jun 28, 2018 7:48 PM (in response to Júlia Dreher)Júlia,
I'm not sure if this is quite it, but maybe it can give an idea.
Firstly, can fix the Dec2017 value using:
{ FIXED :SUM( IF MONTH([Data])=12 AND YEAR([Data])=2017 THEN [Volume] END) }
Then can try something like this for your formula:
IF YEAR(ATTR([Data]))=2018
THEN (SUM([Volume])/SUM([December 2017 Value]))^(12/MONTH(ATTR([Data])))
ELSE (SUM([Volume])/LOOKUP(SUM([Volume]),12))
END
Please see workbook attached in Forum Thread.

274413cagr.twbx 19.1 KB


2. Re: Help with calculations
Júlia Dreher Jul 2, 2018 6:51 AM (in response to swaroop.gantela)Hi,
It´s almost this. The only thing that is different is instead of the Volume I need to use the Rolling 12, and Tableau doesn´t accept it because I´m mixing aggregate and non aggregate functions together. Please find the twbx attached.
Firstly, can fix the Dec2017 value using:
{ FIXED :SUM( IF MONTH([Data])=12 AND YEAR([Data])=2017 THEN [Rolling Average 12] END) }
Then can try something like this for your formula:
IF YEAR(ATTR([Data]))=2018
THEN (SUM([Rolling Average 12])/SUM([December 2017 Value]))^(12/MONTH(ATTR([Data])))
ELSE (SUM([Rolling Average 12])/LOOKUP(SUM([Rolling Average 12]),12))
END
Thanks,
Júlia

274413cagr.twbx 29.5 KB


3. Re: Help with calculations
swaroop.gantela Jul 2, 2018 9:19 PM (in response to Júlia Dreher)Julia,
You can try table calculations instead [Dec 2017 RollAvg]:
WINDOW_MIN(
IF MONTH(MIN([Data]))=12 AND YEAR(MIN([Data]))=2017
THEN [Rolling Average 12] END)
and for [CAGR table calc]
IF YEAR(ATTR([Data]))=2018
THEN ([Rolling Average 12]/[Dec 2017 RollAvg])^(12/MONTH(ATTR([Data])))
ELSE ([Rolling Average 12]/LOOKUP([Rolling Average 12],12))
END
Please see workbook attached in Forum Thread.
Would be grateful if you could post a small table
of what your expected CAGR should be for this sample data.

274413cagr2.twbx 21.3 KB


4. Re: Help with calculations
Júlia Dreher Jul 12, 2018 6:22 AM (in response to swaroop.gantela)Hi,
This calculation is exactly what I need. However, when I try to use the same calculation internally, it doesn´t work. The date settings are the same, but it´s impossible to test the year, so it´s impossible to use this part of the formula: ([Rolling Average 12]/[Dec 2017 RollAvg])^(12/MONTH(ATTR([Data])))
It seems to happen because there´s a lot of dates a month, and in the example there are just one. In this case, do you suggest another way to solve this?
I believe that this test IF YEAR(ATTR([Data]))=2018 and this (12/MONTH(ATTR([Data]))) do not work with multiple dates in the same month.
Regards,
Júlia

5. Re: Help with calculations
Júlia Dreher Jul 13, 2018 7:45 AM (in response to Júlia Dreher)Please find attatched the workbook with more than one date per month. The same problem informed previously happens.
Regards,
Júlia

6. Re: Help with calculations
swaroop.gantela Jul 13, 2018 12:02 PM (in response to Júlia Dreher)Júlia,
Apologies for the delay.
I was not able to find your workbook,
but I tried one that I think is similar with multiple rows per month.
Please see spreadsheet attached and adjust as needed to match yours.
I think that replacing the ATTR with another function like MIN may
give you better results.
Please see workbook attached in the Forum Thread.

274413cagr2.xlsx 8.5 KB

274413cagr3.twbx 30.0 KB
