Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
karthick_hoag
Contributor
Contributor

How to use nested aggregation including set analysis - Little Tricky

Hi,

Please help with the below requirement:

I have a table in excel. Please see the attachment. I need to create a simple straight table with the following requirement.

Year is the Only dimension.

The expression should take the Column "Value", compare it with the column "Mean" and display the Column "Rank". Final output is Year as dimension and Rank as Measure. Column "Value" changes for every year.

Example,  For Year 2017, the Column "Value" is 70. So, 70 should be compared against the column "Mean".  Once the Mean is greater than the Column "Value", then take the corresponding Rank. In this case, for Year 2017, the value 70 exceeds the mean at Rank 39. So for 2017 it should be displayed as 39.

Similarly, for Year 2018, the value 80 exceeds the mean at Rank 89. So for 2018 it should be displayed as 89.

Similarly, for Year 2019, the value 90 exceeds the mean at Rank 99. So for 2019 it should be displayed as 99.

 

Final O/p is:

Year       Rank

2017     39

2018     89

2019    99

 

I have used the following expression, but I am missing something here. So I am not getting the expected result:

=min(aggr(FirstSortedValue({<Mean={">=$(=Only(Value))"}>}Rank,Mean),Year,Mean))

Labels (1)
1 Solution

Accepted Solutions
tresesco
MVP
MVP

Trickiness is in the way of thinking. 🤔😋  

You could probably use a simpler expression like:

=FirstSortedValue(Rank, -if(Mean<=Value,Mean)) 

Capture.PNG 

View solution in original post

2 Replies
tresesco
MVP
MVP

Trickiness is in the way of thinking. 🤔😋  

You could probably use a simpler expression like:

=FirstSortedValue(Rank, -if(Mean<=Value,Mean)) 

Capture.PNG 

karthick_hoag
Contributor
Contributor
Author

Thank you Tresesco. That worked.