topic Re: bad aggr(sum total pivot table in QlikView

bad aggr(sum total pivot table

captain89 — Fri, 17 Jul 2015 16:46:23 GMT

Hi, I've an issue.

My table is like this one below:

Type	KEY_ITEMYYMM	Store	YYMM	QT	VT
2	A01_1501	100	1501	-500	-500
2	A01_1501	888	1501	600	600
2	A02_1501	100	1501	100	100
2	A02_1501	888	1501	-250	-250

I made a pivot table with this expression:

if(aggr(sum([QT]), KEY_ITEMYYMM )<=0, 0, sum(VT))

if the sum of the items in the stores is negative I assume there are no items.

So if I expand all - the expression runs. But if I minimize figures by the first dimension the pivot sum is bad (I want 100 instead of -50).

Type	sum(VT)
2	-500+600+100-250=-50

Type	if(aggr(sum([QT]), KEY_ITEMYYMM )<=0, 0, sum(VT))
2	-500+600+0+0

Can you help me?

Re: bad aggr(sum total pivot table

sunny_talwar — Fri, 17 Jul 2015 16:52:49 GMT

Try this expression:

Sum(Aggr(if(aggr(sum([QT]), KEY_ITEMYYMM )<=0, 0, sum(VT)), KEY_ITEMYYMM, Type))

Re: bad aggr(sum total pivot table

Oleg_Troyansky — Fri, 17 Jul 2015 16:59:06 GMT

Hi Matteo,

there are a few issues with using AGGR() they way you do. For one, you should always include all your chart dimensions in the list of AGGR() dimensions. This may or may not be the specific cause of the problem.

However, instead of describing all the problems with AGGR, I'd like you to rather think in terms of Set Analysis. Your goal is to summarize VT for those Key values that have positive sum of QT - correct? The corresponding Set Analysis condition looks like this:

SUM({<KEY_ITEMYYMM={"=sum([QT])>0"}>} VT)

This Set Analysis condition uses an Advanced Search expression (enclosed in double quotes with an equals sign in the beginning) that only selects those keys that fulfill your condition.

You can read more about various uses of Set Analysis and AGGR in my new book QlikView Your Business that's coming out very soon. You can also attend my lecture on Advanced Aggregation and Set Analysis at the Masters Summit for QlikView - coming this September to New York and Copenhagen.

cheers,

Oleg Troyansky

Re: bad aggr(sum total pivot table

Qrishna — Fri, 17 Jul 2015 17:16:45 GMT

See Attached.

Ho0pe that helps.

Re: bad aggr(sum total pivot table

ramoncova06 — Fri, 17 Jul 2015 17:39:54 GMT

I would also use the expression suggested by Oleg instead of using aggr for this

Re: bad aggr(sum total pivot table

captain89 — Mon, 20 Jul 2015 07:53:11 GMT

Thanks. That's what I need!

Re: bad aggr(sum total pivot table

sasiparupudi1 — Mon, 20 Jul 2015 08:35:11 GMT

Please close this thread by marking the correct answer