bad aggr(sum total pivot table

[captain89]

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?

[sunny_talwar]

Try this expression:

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

[Oleg_Troyansky · Accepted solution]

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

[krishna_2644]

See Attached.

Ho0pe that helps.

[ramoncova06]

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

[captain89]

Thanks. That's what I need!

[sasiparupudi1]

Please close this thread by marking the correct answer