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?






          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:




            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!