6 Replies Latest reply on Nov 2, 2016 7:31 PM by Ivan Young

Need to get the amount for each year by subtracting from previous years

Hi,

I am trying to get the amount for each year by substracting from the previous year: example

In the above screen shot I have in 2016  as 5% complete so the cost divided is 15K out of cost then in 2017 as the same 5% is completed then my cost divided should be 0 then in 2018 my percent complete is 100 then this has to be 2016 (5) - 2017(5-5 = 0) - 2018(5-100=95) = 285000. I am trying to use this in tree map as a label.

its the same logic for 0,75,100 and 0,50,80.

The Cost divided is the desired result that I am looking for.

Any help would be greatly appreciated. Sample workbook is attached.

• 1. Re: Need to get the amount for each year by subtracting from previous years

Hi Rakesh,

I've attached a twbx with 2 ways of accomplish your desired output.  One is LOD based and uses a filter, the other is table calc based and uses a parameter.  If possible I would go with the table calc parameter version as it is self maintaining.  The LOD version is more complex and needs fields for each year.  I'm wrapping up today so if you have any questions I won't reply until tomorrow.

Regards,

Ivan

• 2. Re: Need to get the amount for each year by subtracting from previous years

Thanks Ivan. I am unable to open the file as you have latest version of tableau, but my firm is using 9.0.

• 3. Re: Need to get the amount for each year by subtracting from previous years

I feel your pain Rakesh,  I'm stuck on 9.2 myself.

Attached is a 9.0 version which includes only the table calc example which I've modified to use a filter instead of a parameter.  Let me know if you have any questions.

• 4. Re: Need to get the amount for each year by subtracting from previous years

Thanks Ivan.

• 5. Re: Need to get the amount for each year by subtracting from previous years

It is working Ivan but the calc year field that you created cant be used for all the datasources. Is there any way?

• 6. Re: Need to get the amount for each year by subtracting from previous years

This may be possible using a parameter but you need to tell me about the other data sources.