# New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld May 10-12, Online and Free! REGISTER NOW
cancel
Showing results for
Search instead for
Did you mean:
Creator

## 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

Please help.Thanks,Kizzy

1 Solution

Accepted Solutions
Creator
Author

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

Partner

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

Creator
Author

Hi Youssef,

Thanks but I have the same issue.

Please advise.

Thanks,

Kizzy

Creator
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

Partner

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

MVP

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?

Creator
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

Creator
Author

Hi Sunny,

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

Thanks,

Kizzy

Partner

Please, replace the 4 by Country

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