Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

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
Highlighted
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
Highlighted
MVP
MVP

Just remove the minus before the aggr:

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

Highlighted
Partner
Partner

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

Highlighted
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

Highlighted
MVP
MVP

try below for worst

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

Highlighted
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))

Highlighted
Partner
Partner

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.

Highlighted
Specialist II
Specialist II

Aggr and concat works, PFA

Highlighted
Partner
Partner

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

Highlighted
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