Accumulated calculation with Above()-Statement

[xyz2]

Hi,

I have following problem:

I am trying to calculate the number of 3 different conditions ("New" - "Removed" - "Closed") to get the total number of the status "Open" for each week. Afterwards the results for status "Open" should be accumulated.

For that i try to do it with following formula:

Above(Count(Created_Date) - Count(Removed_Date) - Count(Closed_Date))
+ (Count(Created_Date) - Count(Removed_Date) - Count(Closed_Date))

So each week ist presented in one row and to get the number of new, removed and closed, i am counting the dates for each condition. I though i can get the accumulated results, if i add the above row to my calculation.

But this seems not to work, because i get following results for "Open"

Week	New	Removed	Closed	Open	What i want
2021-15	78	11	60	-	-
2021-16	118	10	99	16	16
2021-17	83	11	70	11	11
2021-18	83	13	67	5	14
2021-19	71	3	66	5	16

e.g. 2021-18 should be calculating like this: 11   +  83 - 13 - 67 = 14, but with my formula i get 5 as result.

Does anyone know what i am doing wrong?

Thanks in advance!

[justISO · Accepted solution]

Hi, not so elegant solution, but maybe this could work for you:

RangeSum(Above(Count(Created_Date)-Count(Removed_Date)-Count(Closed_Date),0,RowNo()-1)) 
+ if(RowNo()=2, Above(Count(Created_Date)-Count(Removed_Date)-Count(Closed_Date)),0)