topic Re: Partial sum with Aggr() in QlikView

Partial sum with Aggr()

Thu, 30 Mar 2017 06:56:53 GMT

Hello All,

I have a pivot table as such

Area, Share Basis, Time Version, Value, Category Total

Category is a field which is above Share Basis in the hierarchy, but is not shown in the pivot table..

I want to do an aggregation on Category, Area and Time Version (which can be same across multiple rows) to get the Category total.

I've been using the following formula.

SUM(AGGR(

SUM([Value]),

[Category],[Area],[Time Version]

)

It is not giving the expected result. It is returning a value only for one row and not the others. Is there any way I can do partial aggregations with AGGR() and how do I get the desired result in this scenario?

Re: Partial sum with Aggr()

tresB — Thu, 30 Mar 2017 07:10:06 GMT

Try using NODISTINCT like:

SUM(AGGR( NODISTINCT

SUM([Value]),

[Category],[Area],[Time Version]

)

Re: Partial sum with Aggr()

Thu, 30 Mar 2017 08:40:46 GMT

Thank you.

Thank you very much. It works. But without the SUM.

With the SUM, the numbers are going haywire.

I can get it till the Share Basis, but for Area, I think I might need to look at options or use a set modifier

Re: Partial sum with Aggr()

sunny_talwar — Thu, 30 Mar 2017 08:47:31 GMT

You can also try this

Sum(TOTAL <[Category], [Area], [Time Version]> [Value])

Re: Partial sum with Aggr()

Thu, 30 Mar 2017 08:52:41 GMT

I've tried that. What happened in that situation was a [Category] was not present in the pivot table dimensions, the result was totalling across Area and Time Version, but not for Category. So I have opted to use AGGR() instead of TOTAL

Re: Partial sum with Aggr()

sunny_talwar — Thu, 30 Mar 2017 11:12:45 GMT

How about like this

Sum(TOTAL <[Category], [Area], [Time Version]> Aggr(Sum(Value), AllDimensionsIncludingCategory))