Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
desere_k
Partner - Contributor II
Partner - Contributor II

Worst Category in dynamic text

Hi can someone please assist me.

I'm trying to find the worst selling category using the Rank function in a text object.

I have already done the best selling category using the following :

='The best selling category is: ' & chr(13) & chr(13) & FirstSortedValue(Category,-aggr(sum(Sales),Category))

The following are the categories ordered according to their sales totals

Capture.PNG

1 Solution

Accepted Solutions
tresesco
MVP
MVP

The working formula.

='The worst selling category is: ' & chr(13) & chr(13)

&FirstSortedValue( {<Sales={'>0'}>}Category,aggr(sum(Sales),Category))

It was not working because of a Category(null) with 0 sales. And 0 sales was becoming the worst sales.

View solution in original post

10 Replies
swuehl
MVP
MVP

Just remove the minus before the aggr:

FirstSortedValue(Category,aggr(sum(Sales),Category))

desere_k
Partner - Contributor II
Partner - Contributor II
Author

Hi Swuehl. It still doesn't return anything if I do that

sundarakumar
Specialist II
Specialist II

Hi,

PFA, it works with Firstsortedvalue,

thought i have tried out with aggr and concat combination. But the First sorted value solution is preferred.

-Sundar

Kushal_Chawda

try below for worst

FirstSortedValue(distinct Category,aggr(sum(Sales),Category))

tresesco
MVP
MVP

Solution provided by Stefan should usually work. If there are multiple categories with worst sales, you might want to try with Disrtinct like:

                               FirstSortedValue( Distinct Category,aggr(sum(Sales),Category))

desere_k
Partner - Contributor II
Partner - Contributor II
Author

I have tried to do what you all have suggested and it still doesn't work.

Please see the attached model. It seems that there is a linking issue because when I drop the DateStoreKey it works.

sundarakumar
Specialist II
Specialist II

Aggr and concat works, PFA

desere_k
Partner - Contributor II
Partner - Contributor II
Author

Thank you it works perfectly. Could you explain your reasoning to it please?

tresesco
MVP
MVP

The working formula.

='The worst selling category is: ' & chr(13) & chr(13)

&FirstSortedValue( {<Sales={'>0'}>}Category,aggr(sum(Sales),Category))

It was not working because of a Category(null) with 0 sales. And 0 sales was becoming the worst sales.