topic Re: Worst Category in dynamic text in QlikView

Worst Category in dynamic text

desere_k — Mon, 21 Dec 2015 11:08:29 GMT

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

Re: Worst Category in dynamic text

swuehl — Mon, 21 Dec 2015 13:53:02 GMT

Just remove the minus before the aggr:

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

Re: Worst Category in dynamic text

desere_k — Tue, 22 Dec 2015 05:01:39 GMT

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

Re: Worst Category in dynamic text

sundarakumar — Tue, 22 Dec 2015 06:33:59 GMT

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

Re: Worst Category in dynamic text

Kushal_Chawda — Tue, 22 Dec 2015 06:39:26 GMT

try below for worst

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

Re: Worst Category in dynamic text

tresB — Tue, 22 Dec 2015 06:39:37 GMT

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

Re: Worst Category in dynamic text

desere_k — Tue, 22 Dec 2015 07:25:27 GMT

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.

Re: Worst Category in dynamic text

sundarakumar — Tue, 22 Dec 2015 07:36:26 GMT

Aggr and concat works, PFA

Re: Worst Category in dynamic text

desere_k — Tue, 22 Dec 2015 07:42:09 GMT

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

Re: Worst Category in dynamic text

tresB — Tue, 22 Dec 2015 08:44:28 GMT

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.

Re: Worst Category in dynamic text

sundarakumar — Tue, 22 Dec 2015 08:51:10 GMT

The explanation is we are trying to sort the category in ascending order with the below expression

(aggr(Category,Category),'|',+aggr(sum(Sales),Category))

pipe is the seperator used, so we are using an index to find the first part which is the first part of ascending

(concat(aggr(Category,Category),'|',+aggr(sum(Sales),Category)),'|')-1

But pls use the First sorted value solution provided by tresesco, bec this might consume more ram than first sorted value.

-Sundar