topic Re: Sort by expression to place same month different year together in App Development

Sort by expression to place same month different year together

wayne-a — Fri, 18 Aug 2017 14:03:24 GMT

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.

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.

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

Anonymous — Fri, 18 Aug 2017 14:59:50 GMT

Try sorting by an expression like this :

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

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

wayne-a — Fri, 18 Aug 2017 18:12:44 GMT

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

Anonymous — Sat, 19 Aug 2017 15:03:23 GMT

Strange - It works fine for me.

Could you post a sample qvf demonstrating it not working ?

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

wayne-a — Mon, 21 Aug 2017 14:19:48 GMT

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)

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

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.

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

wayne-a — Wed, 28 Nov 2018 14:45:21 GMT

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.