Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
cancel
Showing results for 
Search instead for 
Did you mean: 
Tenzin
Contributor
Contributor

Return dimension based on second highest value

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.

Labels (3)
2 Replies
gagewhite
Contributor III
Contributor III

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
Contributor
Contributor
Author

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