Month sort order at edit script

[nihhalmca]

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.

[sunny_talwar]

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

[tresB · Accepted solution]

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.

[nihhalmca]

Hi Tresesco, thanks.

[nihhalmca]

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.

[sunny_talwar]

May be try this

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

[nihhalmca]

Hi Tresesco,

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

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

[nihhalmca]

Hi Sunny.

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

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

[sunny_talwar]

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

[tresB]

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