topic Re: Month Sorting Issue in QlikView

Month Sorting Issue

Anonymous — Tue, 23 Dec 2014 07:54:26 GMT

Hi Everyone,

I have a [cons_month] field in which there are 15th no means ( 1 to 15 ) from 1 to 12 means Jan to Dec month and greater than 12 means Supplementary month (Supp_month) for this i wrote following script

if([Cons Month] = 1,'Jan',

if([Cons Month] = 2,'Feb' ,

if([Cons Month] = 3,'Mar' ,

if([Cons Month] = 4,'Apr',

if([Cons Month] = 5,'May',

if([Cons Month] = 6,'Jun',

if([Cons Month] = 7,'Jul',

if([Cons Month] = 8,'Aug',

if([Cons Month] = 9,'Sep',

if([Cons Month] = 10,'Oct',

if([Cons Month] = 11,'Nov',

if([Cons Month] = 12,'Dec',

if([Cons Month] >12,'Sup_month'))))))))))))) as FactMonth,

This is working fine but when we short this month then its not giving proper ordering of month when we short through

sort tab - load original order then its giving proper order but Dec month is coming before Nov month . like below-

Supp_month, apr, may, jun, jul, aug, sep, oct, dec, nov, jan, feb, mar.

I'm not getting why its coming like that .

Please anyone give me some idea.

Thanks

Re: Month Sorting Issue

anbu1984 — Tue, 23 Dec 2014 08:02:47 GMT

Try sorting on [Cons Month]. If its not working can you post sample app.

Re: Month Sorting Issue

jagan — Tue, 23 Dec 2014 08:05:32 GMT

Hi,

Try like this

If([Cons Month]

Dual(Pick(Wildmatch([Cons Month], 1,2,3,4,5,6,7,8,9,10,11,12, '*')

, 'Jan', 'Feb', 'Mar', 'Apr', .....................'Dec', 'Supp_Month'), [Cons Month]) AS FactMonth

Hope it helps you.

Regards,

Jagan.

Re: Month Sorting Issue

jagan — Tue, 23 Dec 2014 08:06:21 GMT

Now use this as dimension and in sort tab select Numeric Value.

Hope this helps you.

Regards,

Jagan.

Re: Month Sorting Issue

Tue, 23 Dec 2014 08:08:44 GMT

Hello Dhiraj,

May be an issue with the numbers. I almost had the same problem with weeknumbers and solved the problem by using this formula in the master calendar: right('00' & Week(TempDate),2) as Week

Hope this will help you a bit.

Regards,

Martin

Re: Month Sorting Issue

padmanabhan_ram — Tue, 23 Dec 2014 08:18:39 GMT

HI,

In the sort there will be an option Expression, select the [cons Month] field and ascending or descending with respect to your requirement and it should work.

Please let me know if you have nay concern.

Thanks,

Padmanabhan

Re: Month Sorting Issue

Anonymous — Tue, 23 Dec 2014 08:49:24 GMT

Hi jagan Mohan,

Thanks for the reply if condition which u r wrote is not completed in condition place what is need to write.

thanks

Re: Month Sorting Issue

jagan — Tue, 23 Dec 2014 08:55:27 GMT

Hi,

Sorry for the typo mistake

LOAD

Dual(Pick(Wildmatch([Cons Month], 1,2,3,4,5,6,7,8,9,10,11,12, '*')

, 'Jan', 'Feb', 'Mar', 'Apr', .....................'Dec', 'Supp_Month'), [Cons Month]) AS FactMonth

FROM DataSource;

No need of If(), instead we are using Pick.

Regards,

Jagan.

Re: Month Sorting Issue

tresB — Tue, 23 Dec 2014 09:03:41 GMT

Or, a bit simpler solution could be like:

Load

Month(Date#([Cons Month], 'MM')) as NewMonth

Re: Month Sorting Issue

gandalfgray — Tue, 23 Dec 2014 09:31:52 GMT

Hi Dhiraj

As always there's more than one way to do it.

I would go for creating a mapping table, and then use ApplyMap function.

(in this case where you want to sort in numerical order, with a Dual surrounding the ApplyMap - then in your gui-tables specify Numeric value sort))

like this:

// Create the mapping table

num2name:

Mapping

LOAD * INLINE [

F1, F2

1, Jan

2, Feb

3, Mar

4, Apr

5, May

6, Jun

7, Jul

8, Aug

9, Sep

10, Oct

11, Nov

12, Dec

];

...

LOAD ...

Dual(ApplyMap('num2name',[Cons Month],'Sup_month'), [Cons Month]) As [Fact Month],

...

It's a matter of taste of course, but personally I think this is much easier to read and understand then nested if's.

I think it's also more efficient.

/gg

Re: Month Sorting Issue

Anonymous — Tue, 23 Dec 2014 09:52:56 GMT

Hi padmanabhan,

Thanks for the reply in sort tab in expression when we pass [Cons month] its working fine but month order is showing jan ....................dec but I want apr to mar.

Thanks

Re: Month Sorting Issue

padmanabhan_ram — Tue, 23 Dec 2014 10:07:21 GMT

Hi Dhiraj,

Please create an Inline table like below

Load * Inline [

number,Month

04, Apr

05, May

06, Jun

07, Jul

08, Aug

09, Sep

10, Oct

11, Nov

12, Dec

01,Jan

02,Feb

03,Mar

];

and use the field Month or number in the expression instead of [Cons Month].

Note: Please make sure the number and Month data are same in the if statement that you are creating i.e. 04,Apr.

Re: Month Sorting Issue

Tue, 23 Dec 2014 10:11:29 GMT

Hi Dhiraj,

Create One sortorder field and use the same in Sorting Expression

PFA

Re: Month Sorting Issue

Tue, 23 Dec 2014 10:12:41 GMT

HI dheraj,

You need to follow the same thing

just start with apr

You are doing it correctly

Thanks

S shrivas