Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
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