Advanced accumulation with nullvalues

linusblomberg — Fri, 28 Nov 2014 13:56:43 GMT

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.

Re: Advanced accumulation with nullvalues

Gysbert_Wassenaar — Fri, 28 Nov 2014 16:55:08 GMT

See attached qvw for another approach.

Re: Advanced accumulation with nullvalues

linusblomberg — Mon, 01 Dec 2014 07:58:54 GMT

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

Re: Advanced accumulation with nullvalues

linusblomberg — Wed, 03 Dec 2014 15:28:22 GMT

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

topic Advanced accumulation with nullvalues in QlikView

Advanced accumulation with nullvalues

Re: Advanced accumulation with nullvalues

Re: Advanced accumulation with nullvalues

Re: Advanced accumulation with nullvalues