How to use nested aggregation including set analysis - Little Tricky

karthick_hoag — Sat, 16 Nov 2024 03:21:38 GMT

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))

Re: How to use nested aggregation including set analysis - Little Tricky

tresB — Sat, 08 Jun 2019 07:04:46 GMT

Trickiness is in the way of thinking. 🤔😋

You could probably use a simpler expression like:

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

Re: How to use nested aggregation including set analysis - Little Tricky

karthick_hoag — Mon, 10 Jun 2019 22:08:22 GMT

Thank you Tresesco. That worked.

topic Re: How to use nested aggregation including set analysis - Little Tricky in QlikView

How to use nested aggregation including set analysis - Little Tricky

Re: How to use nested aggregation including set analysis - Little Tricky

Re: How to use nested aggregation including set analysis - Little Tricky