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

[fredericvillemi]

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

[Anil_Babu_Samineni]

If you are using >12.0 version, you can try

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

[fredericvillemi]

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

[Anil_Babu_Samineni]

No, With in expression only.

[fredericvillemi]

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

[fredericvillemi]

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

[mrybalko]

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.

[fredericvillemi]

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