4 Replies Latest reply: Oct 22, 2014 5:06 PM by Niklas Hedborg

# Displaying second largest value

hi

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

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

hi

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

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

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