topic Re: Month sort order at edit script in QlikView

Month sort order at edit script

nihhalmca — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I have a Date field (YYYYMM) in fact table and i derived Year and Month fields from Date field in fact table.

need to present Month sort order in UI like Jan, Feb ------, as of now it showing like Apr, Aug -----

have to do sort order at edit script (not at UI).

I have tried dual and applymap functions.

Dual scenario: Month contains 24 fields (12 as text and 12 as number) and no changes if i join dual table with fact as well.

Applymap scenario: I need to do resident or preceding load.

Thanks,

Nihhal.

Re: Month sort order at edit script

sunny_talwar — Tue, 09 May 2017 12:59:20 GMT

Are you creating Month like this?

Month(DateField)?

If you are, then you can just use numeric sort because Month() function created a dual field which does have a underlying numeric value associated with it

Re: Month sort order at edit script

tresB — Tue, 09 May 2017 13:01:51 GMT

Derive you month field or other date fields using proper date functions (rather than any string function) like:

Date(Date#(YourDateText, 'YYYYMM')) as Date,

Month(Date#(YourDateText, 'YYYYMM')) as Month,

...

Then all fields sort should work fine.

Re: Month sort order at edit script

nihhalmca — Tue, 09 May 2017 13:21:17 GMT

Hi Tresesco, thanks.

Re: Month sort order at edit script

nihhalmca — Tue, 09 May 2017 13:25:50 GMT

Hi Sunny,

I have done this way.

Date format like YYYYMM

Date(Date#(Date,'YYYYMM'),'YYYY-MM') as Date,

Right(Date(Date#(Date,'YYYYMM'),'MMM'),3) as Month,

Right(Date(Date#(Date,'YYYYMM'),'YYYY'),4) as Year,

Month(Date) did not give result as expected.

Re: Month sort order at edit script

sunny_talwar — Tue, 09 May 2017 13:29:59 GMT

May be try this

Month(Date#(Date,'YYYYMM')) as Month

Re: Month sort order at edit script

nihhalmca — Wed, 10 May 2017 09:10:22 GMT

Hi Tresesco,

Month names are showing right side in filter pane as number.

Sort order goes wrong If i use Text(Month).

Re: Month sort order at edit script

nihhalmca — Wed, 10 May 2017 09:10:42 GMT

Hi Sunny.

Month names are showing right side in filter pane as number.

Sort order goes wrong If i use Text(Month).

Re: Month sort order at edit script

sunny_talwar — Wed, 10 May 2017 09:12:53 GMT

Why are you using Text(Month) my friend? I am not sure I understand the reason for using Text() function?

Re: Month sort order at edit script

tresB — Wed, 10 May 2017 09:20:16 GMT

If this is only for the sake of alignment, you can adjust that from presentation tab like:

Re: Month sort order at edit script

nihhalmca — Wed, 10 May 2017 09:33:37 GMT

Hi Tresesco,

I think it is not possible in Qlik Sense.

Re: Month sort order at edit script

nihhalmca — Wed, 10 May 2017 09:35:57 GMT

Ok.

Just tried for displaying Left side month names (Jan, Feb ...).

Could you tell how can i display Left side month names in month filter pane (currently it is displaying right side).

Re: Month sort order at edit script

sunny_talwar — Wed, 10 May 2017 10:48:12 GMT

May be use Text(Month) as field and then sort using expression = Month

Re: Month sort order at edit script

nihhalmca — Wed, 10 May 2017 11:10:56 GMT

Thanks, It's working.