topic Re: Re: Displaying second largest value in QlikView

Displaying second largest value

Mon, 20 Oct 2014 08:21:35 GMT

i have a chart which contains incident categories . i need to create norther 5 charts which shows d top 5 categories one by one. d largest i can get it done. but how can i display ONLY the second largest value ???

my equation to calculate the count of categories as follows :

count(if(year(today())=fp_year) and cat_tier='Fail',IncID))

if some one could help me in this issue i would really appreciate it

thank you in advance

Re: Displaying second largest value

Gysbert_Wassenaar — Mon, 20 Oct 2014 08:53:32 GMT

Perhaps this:

count({<MyCategoryDimensionHere={"=rank(count({<fp_year={'$(=year(today))'},cat_tier={'Fail'}>} IncID))=2"}, fp_year={'$(=year(today))'},cat_tier={'Fail'}>} IncID)

or maybe

max(aggr(count({<fp_year={'$(=year(today))'},cat_tier={'Fail'}>} IncID),MyCategoryDimensionHere),2)

Re: Displaying second largest value

Mon, 20 Oct 2014 09:53:59 GMT

i tried this

max(aggr(count({<fp_year={'$(=year(today))'},cat_tier={'Fail'}>} IncID),MyCategoryDimensionHere),2)

but it show falls values but if i remove the dimension in the dimension tab it shows the correct value but it dosent show the category name . what can i do for that??

Re: Re: Displaying second largest value

Anonymous — Wed, 22 Oct 2014 21:00:24 GMT

How about the attached solution?

One expression (for the Bar) similar to MAX(AGGR(SUM(Sales),Market),2)

One expression (to be shown on the axis) similar to ONLY(IF(AGGR(RANK(SUM(Sales),4),Market)=2,Market))

Kind regards

Niklas

Re: Re: Displaying second largest value

Anonymous — Wed, 22 Oct 2014 21:06:00 GMT

Or a bit less complicated... 😉

Use a calculated dimension with IF(AGGR(RANK(SUM(Sales),4),Market)=2,Market)

Suppress Null values

And then just add SUM(Sales) as your expression.

Niklas