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

    Help with calculations

    Júlia Dreher

      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
          swaroop.gantela

          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

           

          Please see workbook attached in Forum Thread.

          1 of 1 people found this helpful
          • 2. Re: Help with calculations
            Júlia Dreher

            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
              swaroop.gantela

              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

               

              Please see workbook attached in Forum Thread.

               

              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
                Júlia Dreher

                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
                  Júlia Dreher

                  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
                    swaroop.gantela

                    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.

                    Please see spreadsheet attached and adjust as needed to match yours.

                     

                    I think that replacing the ATTR with another function like MIN may

                    give you better results.

                     

                    Please see workbook attached in the Forum Thread.