Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
wayne-a
Creator
Creator

Sort by expression to place same month different year together

Hi, I have a barchart on two dimensions, an order date and and order source. 

The order date is a master item defined as follows:  =Date(Monthstart(Received),'MMM-YY')  The field "Received" is a datetime field.

I have the sort defined on the master item numerically and it sorts as expected in date order.Orders by source.JPG

There are instances where someone may want to compare this year to last year or even multiple years for a particular month  I can't add year as another dimension so I wanted to make a version that sorted in a way that would group the months together.  I thought I could just take my existing chart and sort by expression but I've tried many different things and I cannot get it to do this.   Thanks in advance for any suggestions.

1 Solution

Accepted Solutions
wayne-a
Creator
Creator
Author

I ran into this issue again and looked up this post but it seems I never closed the loop on a solution.  What I did was add an additional field in the select sql that did the same thing mentioned above.  I then sorted by expression on that field (no formula).  Not sure why it won't work when putting the formula in to sort by expression.  I suspect this would probably work anywhere in the load script, it was just easiest to add a new column to my sql output.

View solution in original post

5 Replies
Anonymous
Not applicable

Try sorting by an expression like this :

= ( num(month(Received)) *10000 ) + num(year(Received))

wayne-a
Creator
Creator
Author

Hi Bill, thanks for the reply but that doesn't seem to work. It behaves like there there is no sorting set when that expression is applied.

Anonymous
Not applicable

Strange - It works fine for me.

Could you post a sample qvf demonstrating it not working ?

wayne-a
Creator
Creator
Author

Hi Bill, thanks for the reply.  I'll see if I can get a pared down version.  This has a lot of data so I may not be able to.

When I put that expression in as field in a table and sort on it it works correctly (see below)

SortTable.JPG

But removing it and making it the sort expression of the Month-Year field gives results like no sorting is being applied at all.

SortWrong.JPG

One thing I did think of is that Month-Year is a master item.  Maybe an issue sorting by expression on a maser item?  I'll attempt to make it part of the load script and see if that makes a difference.

wayne-a
Creator
Creator
Author

I ran into this issue again and looked up this post but it seems I never closed the loop on a solution.  What I did was add an additional field in the select sql that did the same thing mentioned above.  I then sorted by expression on that field (no formula).  Not sure why it won't work when putting the formula in to sort by expression.  I suspect this would probably work anywhere in the load script, it was just easiest to add a new column to my sql output.