second value for mode()

[StefanoStucchi]

HI

I would try to get the second value for a mode() function

explain:

in a table like this

id	Euro
1	10
2	10
3	10
4	10
5	5
6	5
7	5
8	2
9	2
10	1

the mode of Euro is 10 : mode(Euro) = 10

but how do I find the second value for a mode()?

in the example above it should be 5

I try to do 

mode(if(Euro<>mode(Euro) ,Euro))

but it's a nested aggregation and it's not allowed....

So I try with aggr but sure I'm definitely doing something wrong...

mode(aggr(if(Euro<>mode(Euro),Euro),Euro)) gives me only null values...

any help is appreciated

thanks

[Anil_Babu_Samineni]

@StefanoStucchi Perhaps this?

Max(Aggr(Mode(Euro), Euro), 2)

[StefanoStucchi · Accepted solution]

Hi Anil

Max(Aggr(Mode(Euro), Euro), 2) gives me the second value of Max(Euro), not the second Mode()

Anyway I've find the solution:

firstsortedvalue(Euro,-aggr(count(ID),Euro),2)

thank you !!!