Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
prachisangewar
Creator
Creator

Rank in Qlikview based on Cut off Value

Hi,

I have a report to show show the Top N customers.

The logic is to Show Top N by "Measure1" where "Measure2" is greater than a Cut off Amount.

Users can key in this cutoff amount in an Input Box.

I use the formula below :

=if(aggr(($(vMeasure2)),customer) >= $(vCutoff),IF((Aggr(Rank(($(vMeasure1)),1,1),customer)) <= 20,(Aggr(Rank(($(vMeasure1)),1,1),customer))))

The issue is the formula gets me the Top 20 customer by Measure1 and then filters the Measure2 to be greater  than or equal to cutoff value.

So if out of the list of 20 if only 3 customers have Measure2 greater than or equal to cutoff ,it shows only those 3 in the report.

But the requirement is to filter all customers with Measure2 greater then or equal cutoff and then Rank and show all 20 customers.

I tried to use a Set analysis for this,but gives error.

E.g. : Suppose if I want to see Top 5 and cutoff vale = 2

Attached is the example for the Expected and Actual results.

Please help to suggest if any solution.

Thanks

9 Replies
sunny_talwar

May be this:

=Aggr(If(Rank(If(Measure2 > vCutOff, Measure1), 1, 1) <= 5, Rank(Measure1, 1, 1)), Customer)

Capture.PNG

sunny_talwar

Or better yet, try this:

=Aggr(If(Rank(If(Measure2 > vCutOff, Measure1), 1, 1) <= 5, Rank(If(Measure2 > vCutOff, Measure1), 1, 1)), Customer)


Capture.PNG

Digvijay_Singh
Master III
Master III

Check this also with initial filtering using set expression-

sunny_talwar

Hey Digvijyay -

I am on Apple Mac computer and cannot open your qvw file. Would you be able to share a image and dimension and expression details to show what have you done?

Thanks,

Sunny

prachisangewar
Creator
Creator
Author

Hi Sunny,

Thanks for the suggestion,but it does not serve the purpose the Tank is still no in order of 1,2,3..,there is a break in between.

Please suggest if any other solution.

Thanks !

Digvijay_Singh
Master III
Master III

Hi Sunny,

PFB the details -

Capture.JPG

Rank exp - Rank(Measure1,1,1)

Measure1 - if(rank(Aggr(only({<Measure2={">=$(vTop)"}>}Measure1),Customer),1,1)<=5, Measure1)

Measure2 - if(rank(Aggr(only({<Measure2={">=$(vTop)"}>}Measure1),Customer),1,1)<=5, Measure2)

Regards,

DJ

sunny_talwar

Thanks Digvijay for sharing this. I think these expressions can be further simplified

2) Only({<Customer = {"=Rank(Only({<Measure2={'>=$(vTop)'}>}Measure1),1,1) <= 5"}>}Measure1)

3) Only({<Customer = {"=Rank(Only({<Measure2={'>=$(vTop)'}>}Measure1),1,1) <= 5"}>}Measure2)

Digvijay_Singh
Master III
Master III

Perfect! Thanks.

sunny_talwar

Prachi -

Where do you see a break in Rank? Are you seeing my updated sample

Capture.PNG