Filter straight table based on nth largest Dimension value

[AndersGS]

Hi,

I have a Straight Table in QlikView in which I want to display only the nth largest value in a Dimension.
The dimension I want to evaluate is GroupCode which I also need to add an if statement to, CY_PY = 1.

What I would like to see is for example the second largest GroupCode and its corresponding Expression Value:

GroupCode      GroupCodeName         Expression Value
981                       Name9                             43 282

My data looks like this:

Using QlikView 11.20 SR18

Thanks for your help
Anders

[Arthur_Fong]

There you go:

In expression, try this:

IF(AGGR(RANK(AGGR(SUM(Value),GroupCode)),GroupCode)=2,sum(if(CY_PY=1, Value)),NULL())

[AndersGS]

Thanks for your suggestion! I have just tried it but doesn't seem to be working. This is what I'm using:

=IF(AGGR(RANK(AGGR(Sum(LAGANTTILLG),GroupCode)),GroupCode)=2,sum(if(CY_PY=1,LAGANTTILLG)),NULL())

The GroupCode dimension contains both text and numbers, could that be a problem here?

Regards

Anders

[Arthur_Fong]

You have a sample app?

[AndersGS]

Here you go

[AndersGS]

Sure, here you go! 

[Arthur_Fong · Accepted solution]

Try this in your expression:

if(aggr(RANK(AGGR(SUM(if(CY_PY=1,[sum(LAGANTTILLG)])),GroupCode)),GroupCode)=2,sum(if(CY_PY=1,[sum(LAGANTTILLG)])),NULL())

[AndersGS]

Works fine, thank you!