topic Re: Custom Sorting in QlikView

Custom Sorting

Mon, 04 Aug 2014 14:32:53 GMT

Hi,

I have month on x-axis and two other metrics for comparison 2013-2014 and 2014-2015. I am trying to create custom sort on x-axis as per Australian calendar which starts from July to June.

I am comparing the Sales Value data here is 2013-2014 and 2014-2015.

But not able to implement custom sorting. Pls let me know.

Thanks,

Re: Custom Sorting

Mon, 04 Aug 2014 14:44:09 GMT

Can you post a sample qvw???

Re: Custom Sorting

Anonymous — Mon, 04 Aug 2014 14:48:17 GMT

I would use an Inline table who will be joined to the calendar table

SortMonth:

Load * inline

[Month, SortMonth

July, 1

Aug, 2

...

June, 12

]

left join (Calendar)

load Month, Sort

resident SortMonth

and then in the Sort expression of your diagram use the new field SortMonth

Re: Custom Sorting

juleshartley — Mon, 04 Aug 2014 14:52:36 GMT

...even better is if you can load it as a dual() and then the sorting is seamless.

eg:

Load dual(a,b) inline

[a , b

July, 1

Aug, 2...

]

Re: Custom Sorting

Mon, 04 Aug 2014 14:58:21 GMT

To refresh the qvw file it will take 90 minutes, can I do something at the UI level.

Thanks,

Re: Custom Sorting

Anonymous — Mon, 04 Aug 2014 15:01:09 GMT

you can put the expression in your sort expression:

if (month(yourdate)='July'),1

if (month(yourdate)='August', 2,

....

if (month(yourdate)='June', 12)

But next time before you refresh your qvw, i would put in the the script

Re: Custom Sorting

Mon, 04 Aug 2014 15:49:03 GMT

I already have this logic, but not working as expected.

=IF(Month(InvDate)='Jul',1,IF(Month(InvDate)='Aug',2,IF(MonthInvDate)='Sep',3,IF(Month(InvDate)='Oct','4',IF(Month(InvDate)='Nov',5,

IF(Month(InvDate)='Dec',6,IF(Month(InvDate)='Jan',7,IF(Month(InvDate)='Feb',8,IF(Month(InvDate)='Mar',9,IF(Month(InvDate)='Apr',92,

IF(Month(InvDate)='May',93,IF(Month(InvDate)='Jun',94))))))))))))

Re: Custom Sorting

Anonymous — Mon, 04 Aug 2014 16:10:21 GMT

maybe to many ifs?

try

if (num(month(InvDate))>6, num(month(InvDate))-6, (num(month(InvDate)))+6)

Re: Custom Sorting

Mon, 04 Aug 2014 17:12:43 GMT

No, didn't worked...

Thanks,

Re: Custom Sorting

aveeeeeee7en — Mon, 04 Aug 2014 17:31:46 GMT

Hi Madu

Add this is Sort Expression:

Dual(Month,Match(Month,'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'))

Here Month is your MonthFieldName

See the Snapshot:

Regards

Aviral Nag

Re: Custom Sorting

Mon, 04 Aug 2014 18:29:07 GMT

I am looking at the Australian Calendar which FY will starts from July to June.

Thanks,

Re: Custom Sorting

aveeeeeee7en — Mon, 04 Aug 2014 19:07:31 GMT

Use something like this:

Load

NewDate,

'Q'&Ceil(Num(Month(AddMonths(NewDate,-6)))/3) AS [Fiscal Quarter],

Month(NewDate) AS [Fiscal Month],

yearname(NewDate,0,7) AS [Fiscal Year]

FROm Table.qvd;

The above script will create your Fiscal Year starting from July to June.

Kindly, change NewDate with your Table FieldName.

Also, see the Attachment.

Regards

Aviral Nag

Re: Custom Sorting

maxgro — Mon, 04 Aug 2014 20:03:22 GMT

try with sort by expression

=match(max(month(InvDate)),7, 8, 9, 10, 11, 12, 1, 2, 3, 4, 5, 6)

Re: Custom Sorting

evan_kurowski — Mon, 04 Aug 2014 20:49:45 GMT

Hello Madu, Here's a quick app that will generate multiple years of offset calendars, just assign the 3 parameters:
-Start year
-No of years to generate
-the AddMonths shift

A combined field of the custom (or calendar) year concatenated with the month number should be enough to enforce chronological sort - i.e. Max(MULTI_YEAR_MONTH_ID). (an alternative using the integer value of the month start of each entry would be fine as well).

If your chart axis is only rendering 12 months at a time, but the data holds more than one year of info you'll have multiple years of data piling up in each MONTH dimension (Jan 2014 and Jan 2015 data both rendering under 'Jan'). If you wanted to extend the x-axis to span multiple years, you now have a field like MULTI_YEAR_MONTH_ID to make sure everyone stays in sequence.

Using a Dual() encapsulated value should work fine for sort expressions, but if you intend to eventually involve that field in comparative set-analysis, Dual() encapsulated data can present issues when used in conjunction with set-analysis.

You can test this on your own, but if you take a set-analysis selection that bookends some ranges, see if you get the same results with a purely numeric encoded field vs. a dual().

This expression will work while MULTI_YEAR_MONTH_ID is a purely numeric field:

Sum( {<MULTI_YEAR_MONTH_ID={">= 201501 <= 201504"}>} AMOUNT)

Then converting the entries in MULTI_YEAR_MONTH_ID to encapsulate Dual() using entries like:

LOAD Dual('Jan 2015',201501) AS MULTI_YEAR_MONTH_ID AUTOGENERATE(1);
LOAD Dual('Apr 2015',201504) AS MULTI_YEAR_MONT_ID AUTOGENERATE(1);

If I try the above expression again after converting MULTI_YEAR_MONTH_ID to Dual(), the set-analysis seems to want to evaluate the textual portion of the dual() data type and doesn't pick up on the numeric values (even if you use some explicit data type reference like Sum({<MULTI_YEAR_MONTH_ID={">= $(=Num(Min(MULTI_YEAR_MONTH_ID)))"}>} AMOUNT), and now you can see the set-analysis syntax burgeoning).

That's why even though it seems convenient to combine some date fields in dual, why they may have been split off. Date range Set-analysis works most consistent, and the syntax remains minimal, if it is constructed around purely numeric fields and you don't get entangled in the mess of needing to craft expressions that convert back and forth between format strings and their underlying numeric representations.