Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. $300 savings extended to February 9th Learn More
New Contributor III

FirstSortedValue and set analysis

I am trying to display the region with the max value, but filtered by a specific type.  I have the following formula which finds the highest region based on count of IDs.

FirstSortedValue(DISTINCT Region,-Aggr(count(ID),Region))

Now I need to limit the above formula to evaluate where "Type" column is only "Robo".  I tried using set analysis, but was unsuccessful:

=FirstSortedValue(DISTINCT Region,-Aggr(count({<Type={'Robo'}>}ID),PROVINCIA))  

How do I find the Region with the highest count of IDs where Type is only 'Robo'?

Labels (2)
1 Reply

Re: FirstSortedValue and set analysis

Your formula should work, Is that not working for you?

=FirstSortedValue(DISTINCT Region,-Aggr(count({<Type={'Robo'}>}ID),PROVINCIA))  

If it is not working, Perhaps restrict at end of dimension as well

=FirstSortedValue({<Type={'Robo'}>} DISTINCT Region,-Aggr(count({<Type={'Robo'}>}ID),PROVINCIA))  


=FirstSortedValue({<Type={'Robo'}>} DISTINCT Region,-Aggr(NODISTINCT count({<Type={'Robo'}>}ID),PROVINCIA))  

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)