Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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)
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)