topic Re: How to sort by month when using two dimensions and one expression ? in QlikView

How to sort by month when using two dimensions and one expression ?

fredericvillemi — Sat, 16 Nov 2024 04:16:21 GMT

Hello,

in QlikView

I have a bar chart with two dimensions grouped : Month, Company

and one expression : Count(distinct Ticket_ID)

I would like to show the Count(Distinct Ticket_ID) of each company for each month and I want to sort the months ascending AND the company by descending value of Count

The Ascending sort of Month is very easy but the sort of the companies which is defined in Sort by Expression doesn't work as I would like it to : it sorts the companies based on the total Count(distinct) instead of the count(distinct) of the "current" month.

What can I do to make a descending sort based on the count of each company only for the month in the first dimension ?

Is it a TOTAL or something like that to add in the Sort by Expression ?

I have seen a solution with Dual and Aggr but it doesn't seem to work well in my project

Thanks

Re: How to sort by month when using two dimensions and one expression ?

Anil_Babu_Samineni — Tue, 05 Mar 2019 09:47:49 GMT

If you are using >12.0 version, you can try

Aggr(Count(distinct Ticket_ID), (Month, (Numeric),ASC), (company, (TEXT), Desc))

Re: How to sort by month when using two dimensions and one expression ?

fredericvillemi — Tue, 05 Mar 2019 10:12:40 GMT

Hello,

thanks for your proposal, I'm using 12.1

Where should I put this line ? in the sort expression ? it doesn't seem to work

Re: How to sort by month when using two dimensions and one expression ?

Anil_Babu_Samineni — Tue, 05 Mar 2019 10:18:30 GMT

No, With in expression only.

Re: How to sort by month when using two dimensions and one expression ?

fredericvillemi — Tue, 05 Mar 2019 10:25:58 GMT

Hmmm no, it doesn't change anything, it is the same as using the normal count(distinct )

Re: How to sort by month when using two dimensions and one expression ?

fredericvillemi — Tue, 05 Mar 2019 10:52:44 GMT

Here is an example of Qlikview document

I would like the biggest company to be the first for each month. (in the example, Microsoft should be first on 2019/02)

Drive link to qvw

Re: How to sort by month when using two dimensions and one expression ?

mrybalko — Tue, 05 Mar 2019 13:10:48 GMT

Hello @fredericvillemi

As far as I know it is not possible to sort separately inside each dimension value.

I can suggest the following solution. It is a bit tricky, but maybe fit you.

Re: How to sort by month when using two dimensions and one expression ?

fredericvillemi — Tue, 05 Mar 2019 13:24:22 GMT

Thanks for your proposal but I feel it's a bit too tricky for me : I want to apply it on several charts and I would not like to make tricks on all of them

I tried with Set Analysis, but I can't imagine it's not possible to do it 😞

thanks anyway