
1. Re: How Can I calculate growth difference using calculated field
Simon Runc Aug 4, 2016 10:05 AM (in response to zhengyuan.chen)hi Zhengyuan,
So here's a solution using Last()...although this solution very much depends on GDP being the last sector in your list.
So I've broken the formula into a few steps so you can see how it works, but you can nest into one as I've done in the final version.
So the first thing we want to do is populate all rows with the CAGR from GDP...and this is where it being last helps.
we can use the formula
LOOKUP([CAGR],LAST())
Setting it up to compute using sector. As the CAGR is a table calculation too (run with compute using year), when we have them nested Tableau gives us the option to run them different ways (I've put a redbox round the drop down where we can choose to run each table calculation differently)
So then we want to take each county CAGR away from this figure, which is just
[CAGR]  LOOKUP([CAGR],LAST())
Now the problem here is that it brings back a 0.0% for the GDP row...we could do something funky with custom formatting (shown below), but if any other values were zero they would be blank!
so we can use the last() again to test if the row is the GDP row, so I have
[GDP Row]
LOOKUP(ATTR([Sector]),Last())=ATTR([Sector])
and again this is run by sector.
So now we can nest the whole thing into the final
[CAGR var to GDP]
IF [GDP Row] THEN NULL ELSE
[CAGR]  LOOKUP([CAGR],LAST())
END
I've left the working formulas in to help, but you can remove these in your final version.
It's a bit fiddley, and takes on some fairly advanced features of table calculations, but hopefully makes sense. If not please post back

GDP growth diff  SR.twbx 24.1 KB


2. Re: How Can I calculate growth difference using calculated field
zhengyuan.chen Aug 4, 2016 12:12 PM (in response to Simon Runc)Hi Simon,
Thank you for your help!

3. Re: How Can I calculate growth difference using calculated field
zhengyuan.chen Aug 4, 2016 12:38 PM (in response to Simon Runc)Hi Simon,
A follow up question.
If I have two years of data, like below.
How to delete the calculated fields for the year 2018?
Thank you so much!

GDP growth diff  SR jc.twbx 24.2 KB


4. Re: How Can I calculate growth difference using calculated field
Simon Runc Aug 5, 2016 1:08 AM (in response to zhengyuan.chen)So you can just bring year (and probably easiest to covert to discrete first) onto the filter shelf and deselect 2018. If you still want the formulas to have access to 2018 data then you can hide, or use a Table Calc filter. I won't go into those options if all you want to do is filter out 2018.