Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
Anonymous
Not applicable
Author

Hi All,

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


Thanks,

Kizzy

View solution in original post

23 Replies
YoussefBelloum
Champion
Champion

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


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]))

Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

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

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]))

sunny_talwar

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?

Anonymous
Not applicable
Author

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



Anonymous
Not applicable
Author

Hi Sunny,

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

Thanks,

Kizzy

OmarBenSalem

Please, replace the 4 by Country

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