6 Replies Latest reply on Jul 13, 2018 12:02 PM by swaroop.gantela

Help with calculations

Hi, people,

I´ve built the atttched workbook, were there´s a spreadsheet called 'Volume' and another one called 'Rolling 12'.

There are values from december to may in the 'Rolling 12' spreadsheet. The calculation I need to make is based on the values from this spreadsheet:

Calculation: From december 2018 on the calculation changes.

January 18 = (jan/dez)^(12/1)

February 18 = (fev/dez)^(12/2)

March 18 = (mar/dez)^(12/3)

April 18 = (apr/dez)^(12/4)

May 18 = (may/dez)^(12/5)

June 18 = (jun/dez)^(12/6)

July 18 = (jul/dez)^(12/7)

August 18 = (aug/dez)^(12/8)

September 18 = (sep/dez)^(12/9)

October 18 = (oct/dez)^(12/10)

November 18 = (nov/dez)^(12/11)

December 18 = (dez 2018/dez 2017)

January 19= (jan 2019/jan 2018)

February 19= (fev 2019/fev 2018)

March 19= (mar 2019/jmar 2018)

April 19= (apr 2019/apr 2018)

...

How can I solve it?

Thanks!

Júlia

• 1. Re: Help with calculations

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

1 of 1 people found this helpful
• 2. Re: Help with calculations

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

• 3. Re: Help with calculations

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

Would be grateful if you could post a small table

of what your expected CAGR should be for this sample data.

1 of 1 people found this helpful
• 4. Re: Help with calculations

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

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

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.