2 Replies Latest reply on May 8, 2018 11:50 AM by Javier Gironella

    Count+1 previous step based on Status

    Javier Gironella

      I am pretty sure there is a simple way to solve this, but I can't seem to find it.

       

      I have a workbook with a column that indicates which step is each one of our projects in, so we can keep track of them.

       

      They are asking me to build a visualization in which they want to assume that if a project has reached "Step 10", it also counts as that project having gone through Steps 9, 8, 7, 6 ,5 ,4, etc.; so a project in Step 10 must also add +1 to the count of projects in Step 9, 8, 7, 6, 5, 4 etc.

       

      My data is not structured that way, it only presents the furthest step that the project has reached, so I need to find some workaround for this.

       

      I have tried using calculated fields with =COUNT(IF()) but when structuring them visually I do not get the expected result.

       

      Does anyone have any ideas how to solve this?

       

      Thanks for your help!

        • 1. Re: Count+1 previous step based on Status
          Shinichiro Murakami

          HI Javier

           

          This might work?

           

           

           

          Thanks,

          Shin

          1 of 1 people found this helpful
          • 2. Re: Count+1 previous step based on Status
            Javier Gironella

            Hi Shinichiro,

             

            Sorry about my late answer.

             

            Your formula is exactly what I was looking for, thank you very much!

             

            I would just like to clarify a few points in case anyone stumbles upon this issue and finds this answer:

             

            1. Because this is a WINDOW_SUM, for the calculation to work correctly, the steps must be precisely ordered in the visualization from Step 1 to Step N.

             

            2. If you need to calculate percentage of projects starting from base 100% (Step 1 is 100% of total projects and decreases every step), you can modify Shinichiro's calculation to the following:

             

            WINDOW_SUM(COUNTD([Id]),0,LAST())/WINDOW_SUM(COUNTD([Id]),FIRST(),LAST())

             

            3. If you need to order your steps in rows, from top to bottom (as in Shinichiro's example, Step 1 is the top row and Step N is bottom row), you need to compute using Table (down). If you presented your steps as columns (Step 1 is the first column, Step N is the last column), compute using Table (across).

             

            I hope anyone who sees this finds Shinichiro Murakami's answer as valuable as I did, thanks for your help again!