Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
Showing results for 
Search instead for 
Did you mean: 
Contributor III
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

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)