Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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
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.
Hi Tresesco, thanks.
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.
May be try this
Month(Date#(Date,'YYYYMM')) as Month
Hi Tresesco,
Month names are showing right side in filter pane as number.
Sort order goes wrong If i use Text(Month).
Hi Sunny.
Month names are showing right side in filter pane as number.
Sort order goes wrong If i use Text(Month).
Why are you using Text(Month) my friend? I am not sure I understand the reason for using Text() function?
If this is only for the sake of alignment, you can adjust that from presentation tab like: