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

Count+1 previous step based on Status

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?

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

HI Javier

This might work?

Thanks,

Shin

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

Hi Shinichiro,

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!