Hi, I've been battling a problem that is getting urgent to solve. I need to accumulate orders per week per Category. Category A will only accumulate the A values etc. I'm using aggr(rangesum(above from 0 to rowno() per week and category. This works fine until the category doesn't have any orders in a week. The aggr() funktion returns a null value.
This is part of a larger expression where I will need to have all values aggregated per Week and category so basing my expression on another column will not work.
This works but leave gaps in the table.
Expression 1:
aggr(rangesum(above(TOTAL($(vPO)
),0,rowno())), Category,Week)
I tried to set null values to 0 but the best I get duplicates my values.
Expression 2:
aggr(rangesum(above(TOTAL(
if($(vPO)=0,0,
aggr(rangesum(above(TOTAL($(vPO)
),0,rowno())), Category,Week))
),0,rowno())), Category,Week)
As you see on week 49 A, it should be reduced by the value the previous week.
Data:
| Week | Category | PO | Expression 1 | Expression 2 |
| 2014/48 | A | 29435 | 29435 | 29435 |
| B | 0 | | 0 |
| C | 25760 | 25760 | 25760 |
| D | 0 | | 0 |
| E | 0 | | 0 |
| 2014/49 | A | 46690 | 76125 | 105560 |
| B | 0 | | 0 |
| C | 7517091 | 7542851 | 7568611 |
| D | 45474 | 45474 | 45474 |
| E | 860768 | 860768 | 860768 |
| 2014/50 | A | 1131839 | 1207964 | 1313524 |
| B | 0 | | 0 |
| C | 0 | | 7568611 |
| D | 356400 | 401874 | 447348 |
| E | 3556173 | 4416941 | 5277709 |
| 2014/51 | A | 135604 | 1343567 | 2657091 |
| B | 0 | | 0 |
| C | 0 | | 7568611 |
| D | 0 | | 447348 |
| E | 1417217 | 5834158 | 11111866 |
| 2014/52 | A | 0 | null() | 2657091 |
| B | 628178 | 628178 | 628178 |
| C | 0 | | 7568611 |
| D | 0 | | 447348 |
| E | 0 | | 11111866 |
| 2015/01 | A | 0 | null() | 2657091 |
| B | 0 | | 628178 |
| C | 0 | | 7568611 |
| D | 0 | | 447348 |
| E | 73130 | 5907288 | 17019155 |
| 2015/02 | A | 0 | null() | 2657091 |
| B | 11595 | 639773 | 1267951 |
| C | 2133511 | 9676362 | 17244973 |
| D | 337820 | 739694 | 1187043 |
| E | 0 | | 17019155 |
Very grateful for any help.
