Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
See attached qvw for another approach.
See attached qvw for another approach.
Thank you. I will try your solution tomorrow but it looks like it should work.
Thanks Gysbert Wassenaar Your solution works but it was a bit tricky for me to make it fit in my larger expression but I think it works now. Many thanks. /Linus