Top 5 Aggr by Customer

alejo13qv — Tue, 22 Sep 2015 13:02:15 GMT

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.

Re: Top 5 Aggr by Customer

sinanozdemir — Tue, 22 Sep 2015 13:08:46 GMT

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

Re: Top 5 Aggr by Customer

alejo13qv — Tue, 22 Sep 2015 13:16:01 GMT

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

topic Re: Top 5 Aggr by Customer in QlikView

Top 5 Aggr by Customer

Re: Top 5 Aggr by Customer

Re: Top 5 Aggr by Customer