- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
bad aggr(sum total pivot table
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?
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Try this expression:
Sum(Aggr(if(aggr(sum([QT]), KEY_ITEMYYMM )<=0, 0, sum(VT)), KEY_ITEMYYMM, Type))
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
See Attached.
Ho0pe that helps.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I would also use the expression suggested by Oleg instead of using aggr for this
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks. That's what I need!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Please close this thread by marking the correct answer