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: 
alejo13qv
Contributor III
Contributor III

Top 5 Aggr by Customer

Hi Colleagues,

I have a straight table that shows average daily usage by Customer (being Customer the unique Dimension), and need to specify TOP 5 providers for each Customer.

The qvw has 8 days of data and I have created one expression for each day, such as:

    count({<Status={'UC'},ValidSession={'1'},SDate={">=$(=Date(Today()-7))<$(=Date(Today()-6))"}>}DISTINCT SessionId)

     / count({<ValidSession={'1'},SDate={">=$(=Date(Today()-7))<$(=Date(Today()-6))"}>}DISTINCT SessionId)


Depending on the Status of each Session (SessionId) it might be declared as ValidSession or FailedSession, this expression works fine.


I have an expression to identify Top 5 Providers by Customer for the 8 days, that works fine when filtering one Customer (image Top5_Providers_Customer1), however when selecting more than one, those Top 5 Providers are re-calculated based on amount of customers selected.

         

=concat(

       aggr(

            if(rank( count({<Status={'UC'},ValidSession={'1'} >}DISTINCT SessionId)

                       / count({<ValidSession={'1'}>}DISTINCT SessionId)) <6,ProviderCd),ProviderCd),',')

I have tried using GetSelectedCount(Customer)=1 and GetSelectedCount(Customer)>1 in the expression with the use of the If() function and the Conditional without success, mainly because I cannot figure a way to tell this expression how to calculate for each Customer instead of Top 5 for all customers selected.

Any suggestions on how to update this expression and/or calculate this Top 5 in another way will be greatly appreciated!

Many thanks,

Alejandro.

2 Replies
sinanozdemir
Specialist III
Specialist III

Hi Alejandro,

just add 1 in your set analysis so that the ranking won't change based on filters:

=concat(

      aggr(

            if(rank( count({1<Status={'UC'},ValidSession={'1'} >}DISTINCT SessionId)

                      / count({1<ValidSession={'1'}>}DISTINCT SessionId)) <6,ProviderCd),ProviderCd),',')



Hope this helps.

Thanks

alejo13qv
Contributor III
Contributor III
Author

Hi Sinan,

Thanks for your suggestion, by adding 1 it excludes the user selection which works if the purpose is to have a static table.

However I need it to react to any selection made to Customer filter by the user.

Thanks,

Alejandro