List month by order

[Cassie_L]

Dear all,

my original field for month from source is showing 001, 002, 003, ..., 010, 011,012, so I create below mapping table, and apply map for the field.

ApplyMap('Mappingpostingperiodtomonth',Field'Not Assigned') as Month

But from the visual layer, it came out Nov, Jan, Feb, Mar, ..., Oct. Not sure why Nov become the first one and it didn't follow my list order.

Mappingpostingperiodtomonth:
Mapping Load *
Inline [
MonthNo,Longmonth
001, Jan
002, Feb
003, Mar
004, Apr
005, May
006, Jun
007, Jul
008, Aug
009, Sep
010, Oct
011, Nov
012, Dec];

[Mark_Little · Accepted solution]

Hi @Cassie_L 

Most likely down to the Measure, Just go to sort and sort my expression and add your field MonthNo should sort it as you require.

[marcus_sommer · Accepted solution]

The mapping won't define the ordering else either the load-order or the numeric value respectively the alphanumeric value - and in your case your values are strings and an alphanumeric ordering would be applied, if not the load-order is chosen.

In your case you may not mandatory need this mapping else you may just convert the string into a numeric value or maybe a dual() value if this 3-digits interpretation is really wanted. It might look like:

dual(YourValue, num#(YourValue)) as Value

Beside this if there is also a date available an association to a master-calendar might have further benefits.

- Marcus

[rubenmarin · Accepted solution]

Hi, that could be because there is no sort option for the field and is sorting bi load order, or becasuse other selections are filtering data.

You can add a trick to first load the inline data to keep the load order, and remove the table after the facts table, like:

sortMonths:
LOAD * inline ...

Mappingposting...:
Mapping load * resident sortMonths;

DataTable:
LOAD ...
  Applymap('Mappingpos....
...;

DROP Table sortMonths;

Another option is create the field using the Month() function this creates a Dual value, this is a value that is a number and a text, so the same field knows it's sorting, you can create as:

Month(Makedate(2000,MonthNo)) // You can set any year, we only need the month.

Maybe you need to convert the field to number:

Month(Makedate(2000,Num#(MonthNo,'0'))) // You can set any year, we only need the month.

[Cassie_L]

Hi, Mark, 

In the soring, I use below expression and it works! Thank you.

Match(Month, 'Jan', 'Feb', 'Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec')

[Cassie_L]

Thank you, ruben, 

Month(Makedate(2000,Num#(MonthNo,'0')))

By applied Month(Makedate92000, Num#(Myfield,'0')))

It  shown JAN FEB MAR and have correct order now. Thanks,