topic Re: Aggr() and if() in QlikView

Aggr() and if()

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi Everyone,

I am trying to build a bar chart with defined dimensions. The dimensions are defined by =valuelist('Top','Rest') , and the measures should be presented as the sum of Invoice Amounts of top 5 performers and sum of Invoice Amounts of the rest.

I tried to define this as the script shown below. For the 'Top' part I am getting a correct value, but the second part is showing empty.

if( valuelist('Top','Rest')='Top',

sum(aggr( IF( aggr ( Rank(sum([Invoice Amount])),[Customer Name])<=5,

sum([Invoice Amount])),[Customer Name])),

sum(aggr( IF( aggr ( Rank(sum([Invoice Amount])),[Customer Name])>5,

sum([Invoice Amount] )),[Customer Name]))

)

Could anyone help me out with how to follow from here on?

Thanks!

Edil

Re: Aggr() and if()

sunny_talwar — Wed, 08 Feb 2017 10:08:26 GMT

Look here:

Q-Tip # 14 – AGGR() and Synthetic Dimensions | Natural Synergies

Re: Aggr() and if()

Wed, 08 Feb 2017 10:09:41 GMT

Hi Try This,

if( valuelist('Top','Rest')='Top',
IF( aggr ( Rank(sum([Invoice Amount])),[Customer Name])<=5,sum([Invoice Amount])),[Customer Name])),

IF( aggr ( Rank(sum([Invoice Amount])),[Customer Name])>5, sum([Invoice Amount] )),[Customer Name])))

pls correct the syntax if i am wrong else could u pls share your application

Re: Aggr() and if()

Anonymous — Wed, 08 Feb 2017 11:21:24 GMT

Thanks Sunny T,

So instead of using a Synthetic Dimension (i.e =valuelist('Top','Rest')), which turned out to be tricky to work together with when using Aggr() function, I chose a simpler way. Which is:

Dimension:

if( aggr ( Rank(sum([Invoice Amount])),[Customer Name])<=5, 'Top', 'Rest');

Measure:

sum([Invoice Amount])

This easily does the thing I need.

I think I just overthought the difficulty of the project.