topic Re: Filter straight table based on nth largest Dimension value in QlikView

Filter straight table based on nth largest Dimension value

AndersGS — Sat, 16 Nov 2024 01:59:04 GMT

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

Re: Filter straight table based on nth largest Dimension value

Arthur_Fong — Tue, 12 Nov 2019 01:10:05 GMT

There you go:

In expression, try this:

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

Re: Filter straight table based on nth largest Dimension value

AndersGS — Tue, 12 Nov 2019 10:12:22 GMT

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

Re: Filter straight table based on nth largest Dimension value

Arthur_Fong — Tue, 12 Nov 2019 10:29:28 GMT

You have a sample app?

Re: Filter straight table based on nth largest Dimension value

AndersGS — Tue, 12 Nov 2019 10:43:03 GMT

Here you go

Re: Filter straight table based on nth largest Dimension value

AndersGS — Tue, 12 Nov 2019 10:44:39 GMT

Sure, here you go!

Re: Filter straight table based on nth largest Dimension value

Arthur_Fong — Tue, 12 Nov 2019 11:13:36 GMT

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

Re: Filter straight table based on nth largest Dimension value

AndersGS — Tue, 12 Nov 2019 11:26:53 GMT

Works fine, thank you!