Cannot get AGGR expression to work

[Anonymous]

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

[YoussefBelloum]

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]

Hi Youssef,

Thanks but I have the same issue.

Please advise.

Thanks,

Kizzy

[Anonymous]

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

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

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]

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?