4 Replies Latest reply on Aug 5, 2016 1:08 AM by Simon Runc

    How Can I calculate growth difference using calculated field

    zhengyuan.chen

      Hi,

      I'm hoping someone might be able to help me write a formula to calculate GDP growh difference. I have already calculated CAGR growth rate in Tableau.

      The goal is to create a calculated field where the output = GDP growth rate - consumer/health/IT growth rate .

       

      For example, in the year 2017. I need a new column:

      Consumer:      6.8%-6.5%=0.003

      Health:             6.8%-4.5%=0.023

      IT:                    6.8%-3.4%=0.034

       

       

      Thank you!

       

        • 1. Re: How Can I calculate growth difference using calculated field
          Simon Runc

          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 red-box 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

          2 of 2 people found this helpful
          • 2. Re: How Can I calculate growth difference using calculated field
            zhengyuan.chen

            Hi Simon,

             

            Thank you for your help!

            • 3. Re: How Can I calculate growth difference using calculated field
              zhengyuan.chen

              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!

               

               

              • 4. Re: How Can I calculate growth difference using calculated field
                Simon Runc

                So you can just bring year (and probably easiest to covert to discrete first) onto the filter shelf and de-select 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.