Return dimension based on second highest value

[Tenzin]

Hi,

I am trying to get the dimension [Entity Name] based on the second highest value of Amount, so far I could not get it working.

To get the value I use the below expression , which gives me the correct result even if I have an entity filter on [Entity Name]:
=max(
{<
$(v_Time_CY_CurrentMonth),
$(v_Scenario_Actuals),
$(v_View_YTD),
[Entity Name] = ,
[%Entity_Key] = {'BeLux','Spain','France'},
Account = {'9000000'},
Supporting_KPI_Name=
>}
(Amount ),2)

I have figured out that I can use the below Firstsortedvalue formula to get the dimension [Entity Name] based on the first highest value. But I can't get this working when there is a filter on [Entity Name], with a filter on I can only retrieve the [Entity Name] that is currently filtered. Which is strange as I use "[Entity Name]=," in the expression to ignore [Entity Name] which is used as a filter.

Also is there a way to get with this formula the [Entity Name] based on the second highest Amount?

=firstsortedvalue([Entity Name],-(aggr(sum({<
$(v_Time_CY_CurrentMonth),
$(v_Scenario_Actuals),
$(v_View_YTD),
[Entity Name] = ,
[%Entity_Key] = {'BeLux','Spain','France'},
Account = {'9000000'},
Supporting_KPI_Name=
>}Amount),[%Entity_Key],[Entity Name])))

Thanks in advance.

[gagewhite]

Good Morning!

The question is a bit difficult to answer. If you had some sample data, showed the formulas in your variables listed, and showed expected result vs current result it would help in answering your question. However, I had a similar issue trying to rank values as a dimension. Maybe this will help: https://community.qlik.com/t5/Qlik-Sense-App-Development/Ranking-Year-and-Quarter-for-2-Different-Di...

Cheers!

[Tenzin]

Thank you for your answer but I am looking for something different.

I am looking for the right expression like below but that doesn't return the Amount but the [Entity Name] based on the second highest value. Also this formula should ignore the [Entity Name] filter, this is what I could not get working.

=max(
{<
$(v_Time_CY_CurrentMonth),
$(v_Scenario_Actuals),
$(v_View_YTD),
[Entity Name] = ,
[%Entity_Key] = {'BeLux','Spain','France'},
Account = {'9000000'},
Supporting_KPI_Name=
>}
(Amount ),2)

Example, the below should return [Entity Name] "C" taken into account that the sheet already has a filter active on [Entity Name] A:

[Entity Name], Amount

A 10

B 20

C 15