Hi All,
I am facing an issue with rangesum when we have null values in data.
Issue:
| Month | R1 | R2 |
|---|
| Jan-16 | 10 | |
| Feb-16 | 10 | |
| Mar-16 | 10 | 30 |
| Apr-16 | - | 20 |
| May-16 | 10 | 20 |
| Jun-16 | 10 | 20 |
| Jul-16 | 10 | 30 |
| Aug-16 | 10 | 30 |
| Sep-16 | - | 20 |
| Oct-6 | - | 10 |
| Nov-16 | 10 | 10 |
| Dec-16 | - | 10 |
R2 is total of 3 month sum of R1.
So R2 in Mar-16=R1 Jan-16+R1Feb-16+R1Mar-16
=10+10+10
=30
Which we achieved through applying Rangesum(Above) function. Unfortumately the null functions(- is null value) are not handled properly and we are receing incorrect R2 results.
I am receiving below Data
| YearMonth | R1 | R2 |
|---|
| Jan-16 | 10 | |
| Feb-16 | 10 | |
| Mar-16 | 10 | 30 |
| May-16 | 10 | 30 |
| Jun-16 | 10 | 20 |
| Jul-16 | 10 | 30 |
| Aug-16 | 10 | 30 |
| Nov-16 | 10 | 30 |
My expression is something like this:
Sum(aggr(rangesum(above( sum({<Yearmonth=>}[R1])),0,3)),$(vCurrentDimForecast), YearMonth))
Please let me know if you need more clarity.
Regards,
Nita Prasad
