Announcements
cancel
Showing results for
Did you mean:
Creator

## bad aggr(sum total pivot table

Hi, I've an issue.

My table is like this one below:

TypeKEY_ITEMYYMMStoreYYMMQTVT
2A01_15011001501-500-500
2A01_15018881501600600
2A02_15011001501100100
2A02_15018881501-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

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

Can you help me?

1 Solution

Accepted Solutions

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

6 Replies
MVP

Try this expression:

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

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

Specialist III

See Attached.

Ho0pe that helps.

Specialist III

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

Creator
Author

Thanks. That's what I need!

Master III