Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

kizzydavies
Contributor

Cannot get AGGR expression to work

Hi,

I asked a previous question on how to create a bar chart that displays 1 value but is filtered by another.

An expression was given (see below)

=if( RANK( Sum({$<[Requisition Status]={Closed}>}[Number of Filled Reqs]) )<=5,Sum({$<[Division]={'Lifestyle Audio'}>}[Approve to Close])/Sum({$<[Division]={'Lifestyle Audio'}>}[Number of Filled Reqs]))

The expression works but shows the countries that are not are not in the top 5.  I then got this instruction but I cannot get it to work

=AGGR(if(RANK(SUM(Sales))<=4,Country),Country) and then check suppress null values

QLIK Rank screen shot.PNG


Please help.Thanks,Kizzy

1 Solution

Accepted Solutions
kizzydavies
Contributor

Re: Cannot get AGGR expression to work

Hi All,

The AGGR worked once I removed the the if(sum..... from the dimension and just used the normal measure.


Thanks,

Kizzy

23 Replies
YoussefBelloum
Esteemed Contributor

Re: Cannot get AGGR expression to work

Hi,

try this (and be sure to uncheck null values on the dimension)

=if( RANK(Sum({$<[Requisition Status]={Closed}>}[Number of Filled Reqs]),4 )<=5,Sum({$<[Division]={'Lifestyle Audio'}>}[Approve to Close])/Sum({$<[Division]={'Lifestyle Audio'}>}[Number of Filled Reqs]))

OmarBenSalem
Esteemed Contributor

Re: Cannot get AGGR expression to work


Maybe

=if( RANK( Sum({$<[Requisition Status]={'Closed'}>}[Number of Filled Reqs]), Country )<=5,

Sum({$<[Division]={'Lifestyle Audio'}>}[Approve to Close])/Sum({$<[Division]={'Lifestyle Audio'}>}[Number of Filled Reqs]))

kizzydavies
Contributor

Re: Cannot get AGGR expression to work

Hi Youssef,

Thanks but I have the same issue.

Please advise.

Thanks,

Kizzy

qlik aggr screen shot 1.PNG

qlik aggr screen shot 2.PNG

kizzydavies
Contributor

Re: Cannot get AGGR expression to work

Sorry, here is the full expression

if( RANK(Sum({$<[Requisition Status]={Closed},[Division]={'Lifestyle Audio'}>}[Number of Filled Reqs]),4)<=4,Sum({$<[Requisition Status]={'Closed'},[Division]={'Lifestyle Audio'}>}[Approve to Close])/Sum({$<[Requisition Status]={'Closed'},[Division]={'Lifestyle Audio'}>}[Number of Filled Reqs]))

I get the same issue

Thanks,

kizzy

qlik aggr screen shot 1.PNG

qlik aggr screen shot 2.PNG

OmarBenSalem
Esteemed Contributor

Re: Cannot get AGGR expression to work

what is this 4?? in the rank function; you should have 2 parameters; the expression tor ank and the dimension to rank:

rank(Expression,YourDimToRank) which in ur case is Country!

so what is this 4 number?? Instead of Country??

if( RANK(Sum({$<[Requisition Status]={Closed},[Division]={'Lifestyle Audio'}>}[Number of Filled Reqs]),      4           )<=4,Sum({$<[Requisition Status]={'Closed'},[Division]={'Lifestyle Audio'}>}[Approve to Close])/Sum({$<[Requisition Status]={'Closed'},[Division]={'Lifestyle Audio'}>}[Number of Filled Reqs]))

Re: Cannot get AGGR expression to work

Would it be possible for you to share your qvf file or a sample qvf where you can show the issue and we can play around with it a little bit?

kizzydavies
Contributor

Re: Cannot get AGGR expression to work

Hi,

I'm using the following dimension which is just my intepretation of the AGGR expression that was given to me (I don't know if this is correct)

=AGGR(if(RANK(SUM({$<[Requisition Status]={Closed},[Division]={'Lifestyle Audio'}>}[Number of Filled Reqs]))<= 4,LS Countries),LS Countries)

the measure is

if( RANK(Sum({$<[Requisition Status]={Closed},[Division]={'Lifestyle Audio'}>}[Number of Filled Reqs]), 4 )<=4,Sum({$<[Requisition Status]={'Closed'},[Division]={'Lifestyle Audio'}>}[Approve to Close])/Sum({$<[Requisition Status]={'Closed'},[Division]={'Lifestyle Audio'}>}[Number of Filled Reqs]))


Thanks,


Kizzy



kizzydavies
Contributor

Re: Cannot get AGGR expression to work

Hi Sunny,

Sorry but there is personal identification information in the file which I cannot share.

Thanks,

Kizzy

OmarBenSalem
Esteemed Contributor

Re: Cannot get AGGR expression to work

Please, replace the 4 by Country

and replace ur dimension, by simply: Country and see what happens?

Community Browser