Qlik Community

Ask a Question

New to Qlik Sense

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

Announcements
QlikWorld starts MONDAY! last chance to register is now ! REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
kizzydavies
Creator
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

QLIK Rank screen shot.PNG


Please help.Thanks,Kizzy

1 Solution

Accepted Solutions
kizzydavies
Creator
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

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

kizzydavies
Creator
Creator
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

kizzydavies
Creator
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

qlik aggr screen shot 1.PNG

qlik aggr screen shot 2.PNG

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

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?

kizzydavies
Creator
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



kizzydavies
Creator
Creator
Author

Hi Sunny,

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

Thanks,

Kizzy

OmarBenSalem
Partner
Partner

Please, replace the 4 by Country

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