Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
wayneantinore-a
Contributor

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
wayneantinore-a
Contributor

Re: Sort by expression to place same month different year together

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.

5 Replies

Re: Sort by expression to place same month different year together

Try sorting by an expression like this :

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

wayneantinore-a
Contributor

Re: Sort by expression to place same month different year together

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.

Re: Sort by expression to place same month different year together

Strange - It works fine for me.

Could you post a sample qvf demonstrating it not working ?

wayneantinore-a
Contributor

Re: Sort by expression to place same month different year together

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.

wayneantinore-a
Contributor

Re: Sort by expression to place same month different year together

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.