Skip to main content
Announcements
Marching toward a simplified navigation! READ ON
cancel
Showing results for 
Search instead for 
Did you mean: 
fredericvillemi
Creator III
Creator III

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

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

 

Labels (3)
7 Replies
Anil_Babu_Samineni

If you are using >12.0 version, you can try

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
fredericvillemi
Creator III
Creator III
Author

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.

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
fredericvillemi
Creator III
Creator III
Author

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

fredericvillemi
Creator III
Creator III
Author

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
Creator II
Creator II

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.

sorting.JPG

fredericvillemi
Creator III
Creator III
Author

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