10 Replies Latest reply on Apr 5, 2012 7:18 AM by Alex Kerin

Calculate Monthly Value from Consolidated Value

Hi all,

Is there a way how to create a calculated field from the consolidated value as sample below into the monthly level value?

 MONTH VALUE JAN 135428246.767459 FEB 242553721.016045 MAR 360145889.873168 APR 494044416.841853 MAY 610270608.203284 JUN 726563041.15088

• 1. Re: Calculate Monthly Value from Consolidated Value

--Shawn

• 2. Re: Calculate Monthly Value from Consolidated Value

Hi Shawn,

Sorry for any unclear statement in earlier post, here is the calculated field that I would like to get, basically how can we get this calculation done in Tableau?

Rgds,

Limbs

• 3. Re: Calculate Monthly Value from Consolidated Value

This should cheer Joe up a bit, I actually used a table calc to do the calculate value column. (see attached)

And no I wasn't able to quickly figure a way to deal with the NULL in the first row, but I do vaguely remember this being discussed on these forums before. Hope this gets you started Limbs.

--Shawn

1 of 1 people found this helpful
• 4. Re: Calculate Monthly Value from Consolidated Value

Thanks a lot. However, anyone can figure out how can we retain the value of 1st row (JAN)?

• 5. Re: Calculate Monthly Value from Consolidated Value

sum([Value])- zn(lookup(sum([Value]),-1)) would do it

• 6. Re: Calculate Monthly Value from Consolidated Value

Hi Alex,

Thanks a lot. It's work!

Rgds,

Limbs

• 7. Re: Calculate Monthly Value from Consolidated Value

Alex, if i use the calculated value to get the variance as new column (month on month) as below, how's the work around?

• 8. Re: Calculate Monthly Value from Consolidated Value

Same again:

sum([calculated value])-zn(lookup([calculated value],-1) should work

• 9. Re: Calculate Monthly Value from Consolidated Value

Alex, error message appear: Argument to SUM(an aggregate functio) is aready an aggregation, and cannot be further aggregated. Any ideas?

-Limbs

• 10. Re: Calculate Monthly Value from Consolidated Value

Get rid of the sum - calc is already aggregated