Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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