Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

linusblomberg
Contributor II

Advanced accumulation with nullvalues

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:

WeekCategoryPOExpression 1Expression 2
2014/48A294352943529435
B0 0
C257602576025760
D0 0
E0 0
2014/49A4669076125105560
B0 0
C751709175428517568611
D454744547445474
E860768860768860768
2014/50A113183912079641313524
B0 0
C0 7568611
D356400401874447348
E355617344169415277709
2014/51A13560413435672657091
B0 0
C0 7568611
D0 447348
E1417217583415811111866
2014/52A0 null()2657091
B628178628178628178
C0 7568611
D0 447348
E0 11111866
2015/01A0 null()2657091
B0 628178
C0 7568611
D0 447348
E73130590728817019155
2015/02A0 null()2657091
B115956397731267951
C2133511967636217244973
D3378207396941187043
E0 17019155

Very grateful for any help.

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Advanced accumulation with nullvalues

See attached qvw for another approach.


talk is cheap, supply exceeds demand

View solution in original post

3 Replies
MVP & Luminary
MVP & Luminary

Re: Advanced accumulation with nullvalues

See attached qvw for another approach.


talk is cheap, supply exceeds demand

View solution in original post

linusblomberg
Contributor II

Re: Advanced accumulation with nullvalues

Thank you. I will try your solution tomorrow but it looks like it should work.

Highlighted
linusblomberg
Contributor II

Re: Advanced accumulation with nullvalues

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