4 Replies Latest reply on Dec 21, 2016 11:56 AM by Eli Blankers

    Cumulative change in time series.

    Yan Wu

      I have daily percent change from which I need to create a chart that has cumulative change.

       

      The formula is (1+change)*previous.

       

      The values all need to start at a single starting point, typically 1 or 100. Therefore my formula becomes the following:

       

      if index() = 1 then 1

           else (1+change)* previous

      end

       

      However, as you can see, this format cuts off the first value in the time series.    

       

      Does anyone know a better solution? Perhaps how can I add 1 to the beginning of the time series?

        • 1. Re: Cumulative change in time series.
          Eli Blankers

          Hi Yan Wu,

           

          If I understand correctly, you want a point value of 1 before your first point in the dataset, and then for your first point in the dataset, you want 1+change, and then previous+change, and so forth.  To do this, you would have to pad your dataset with another point (probably the day before your first point in the dataset) with a value of 1.

           

          Otherwise, you could not display the 1.0 origin point, and start with the first deviation from 1.0, using:

           

          if index()=1 then 1+SUM([Pct chg])

              else (1+sum([Pct chg]))*PREVIOUS_VALUE(1)

          END

           

          If my reply doesn't seem to fit, I may need more information to understand your question fully.

           

          Thanks,

          Eli Blankers

          • 2. Re: Cumulative change in time series.
            Yan Wu

            Yes you understand it fully,t hank you.

             

            Pad my data with another day - can I do this within Tableau?    

                

            It would be bad practice for me to try to alter my dataset natively wouldn't it?

            • 3. Re: Cumulative change in time series.
              Hildebrando Souza Jr

              Hi Yan,

               

              You can pad your data with another day within Tableau by changing the first (or last) day of your data to the day before. Now with a combination of "Showing Missing Values" and a table calculation, you will be able to achieve what you want.

               

              Here are the steps:

              1. Create a calculated field changing your first day to a day before. Your calculation can be like this one:
              2. Rebuild the chart using this new Date (Ajusted) field as discrete dates.
              3. Right click on the date axis and select Show Missing Values.
              4. Now adjust your formula and make it like this:

                            

               

              I also attached the workbook, so you can have a better understanding.

               

              Best,

              Hil

              • 4. Re: Cumulative change in time series.
                Eli Blankers

                Nice Solution, Hil.

                 

                Yes, Yan Wu, I would typically say padding the data at the source wouldn't be a best practice, so Hil's solution may be the right way to go.

                 

                Regards,

                Eli