5 Replies Latest reply on Oct 17, 2018 2:01 PM by Joe Oppelt

    Continous growth over time

    Zainab Usman

      I'm trying to get a graph where over 3 months any certain variable has grown at least 5% each period, but only if continuously grew at least 5% each month for 3 months. The results I am getting are for a certain month only. Need help with the calculated field please.

        • 1. Re: Continous growth over time
          Joe Oppelt

          You probably already have the %growth figured out. 

           

          Next, create a calc that uses the PREVIOUS_VALUE function.

           

          IF [Growth] >= .05 then

            PREVIOUS_VALUE(0)+1

          ELSE 0

          END

           

          This will create a sort of rolling value.  If the current growth is not 5%, set this to zero.  If it is, then grab the previous value of this calc and add 1.

           

          If the previous was not zero, then this will tick the count up 1.  (If it was zero, then this will set the current count to 1.)

           

          When this value is 3 or more, you are in a stretch that is at least 3 months in a row of 5% growth.

          • 2. Re: Continous growth over time
            Zainab Usman

            Hi Joe,

             

            Still not quite there. Can you please confirm how you would set up the % growth?

            • 3. Re: Continous growth over time
              Joe Oppelt

              (SUM([whatever]) - LOOKUP(SUM([whatever]),-1) / LOOKUP(SUM([whatever]),-1)

               

              (today minus yesterday) / yesterday

               

              You could also use a quick table calc, which would generate essentially the same code internally.

              • 4. Re: Continous growth over time
                Zainab Usman

                Yes that's what I did. Attaching the workbook. I only want the "pink" to show up as its the only one more than 5% consecutively.

                 

                Thanks in advance

                • 5. Re: Continous growth over time
                  Joe Oppelt

                  In the attached I made a crosstab so that I could see actual numbers for now.  See Sheet 2.  It looks to me like Grey is the only one that does NOT have a 3-month span.  But you can see how the calc works in Grey.  It resets to zero when you drop below 5%.  With a string of 36 months, for example, you might have several growth strings come and go, and that's probably what you are expecting.

                   

                  On Sheet 2(2), I took the 3-months calc and dragged it to filters.  Selected for min=3.  This will show you any CELL that has 3 or more.  (And it completely throws out Grey since there are no marks at all.)

                   

                  On Sheet 2(3) I made a different sort of calc.  If anywhere in the row there is a 3 or more, then keep the whole row.  Right click on Calculation1 in the filter shelf.  Select "Edit Table Calc".  You will see that on this one I didn't rely on the default of Table(across).  I evaluate this along the dates, and restart every [Colors].  This sets a value of 1 or 0 across all the marks in the entire row for each color, and that way you get the whole set of data for each color that has a 3 or more anywhere in the row.

                   

                  So I'm not sure how you really want to display this in the line chart.  Maybe you want the whole line if there is a 3 anywhere in it.  Or maybe you just want to display the marks that have 3 or more.  But I cage the way to do both approaches.