4 Replies Latest reply on Dec 14, 2016 2:08 PM by Andrew Watson

    Cumulative Change

    Yan Wu

      Hello,

       

      I have data that contains date and percent change [chg] in my data.

       

      I would like to create a chart that is a cumulative change, which means (1+chg) * previous value.

       

      In order to accomplish this, I use the following:

       

      if INDEX()=1 then 1

          ELSE (1+sum([chg]))*PREVIOUS_VALUE(1)

      END

       

      The problem with this format is that it cuts off the first day's data.

       

      Is there a way for me to include the first day's data?

       

      Thank you.

        • 1. Re: Cumulative Change
          Andrew Watson

          It looks like you're telling it to put 1 in as the first value, is that not what you're expecting? It's hard to say what's wrong without some data and not knowing the exact issue. As complete stabs in the dark you could try:

           

          if INDEX()=1 then sum([chg])

              ELSE (1+sum([chg]))*PREVIOUS_VALUE(1)

          END

           

          or

           

          if FIRST()=0 then 1

              ELSE (1+sum([chg]))*PREVIOUS_VALUE(1)

          END

           

          OR

           

          if FIRST()=0 then sum([chg])

              ELSE (1+sum([chg]))*PREVIOUS_VALUE(1)

          END

          1 of 1 people found this helpful
          • 2. Re: Cumulative Change
            Yan Wu

            Thank you. The suggestions are a bit different than what I am after.

             

            See the attached example. I am essentially trying to create a growth chart. That chart should start at 100% since there may be more than one variable.

             

            The example I have works fine except that it misses the first data point.

             

            Any ideas?

            • 3. Re: Cumulative Change
              Andrew Watson

              I don't have tableau on this machine to open your file but this response should bump the thread back to the top and someone should be able to pick it up.

              • 4. Re: Cumulative Change
                Yan Wu

                A machine without Tableau? What kind of world do we live in?