topic Re: Assign numbers to months for custom ordering in QlikView

Assign numbers to months for custom ordering

Mon, 05 Jan 2015 16:44:22 GMT

I want to be able to order my charts using our business' fiscal year. I'm guessing the best way to do this would be to assign each month a custom numeric value so that I could then sort the chart by this value? For example, here is how I would assign the numbers to each month:

1 = July

2 = Aug

3 = Sept

4 = Oct

5 = Nov

6 = Dec

7 = Jan

8 = Feb

9 = Mar

10 = Apr

11 = May

12 = Jun

I'm sure there are multiple methods to accomplish this, so I'm open to any and all suggestions. Thank you.

Re: Assign numbers to months for custom ordering

Mon, 05 Jan 2015 16:47:25 GMT

Can you try chart properties->Sort , there you can uncheck/check the sort behaviour for each dimension and furthermore you can goto sort by expression and write a complex expression. but in your case you can sort by numeric value of monthname

Re: Assign numbers to months for custom ordering

Mon, 05 Jan 2015 16:51:03 GMT

Hi Steve,

I would suggest loading your month as a dual for this, so each month has the text but also an underlying numerical representation, so you can just standard number sorting on the field without the need for custom expression sorting etc.

Dual([My Month text],[My Fiscal Month Number])

hope that helps

Joe

Re: Assign numbers to months for custom ordering

Mon, 05 Jan 2015 16:52:08 GMT

Thank you Mario! Simple solution that I just overlooked.

Re: Assign numbers to months for custom ordering

Mon, 05 Jan 2015 16:56:10 GMT

One option might be to create a dual value for month, such as:

[Fiscal Month]:

LOAD Dual(Month,MonthID) as Month;

LOAD * Inline [

MonthID,Month

1,Jul

2,Aug

3,Sep

4,Oct

5,Nov

6,Dec

7,Jan

8,Feb

9,Mar

10,Apr

11,May

12,Jun

];