topic List month by order in App Development

List month by order

Cassie_L — Tue, 08 Nov 2022 02:40:58 GMT

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];

Re: List month by order

Mark_Little — Tue, 08 Nov 2022 07:27:09 GMT

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.

Re: List month by order

marcus_sommer — Tue, 08 Nov 2022 07:36:16 GMT

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

Re: List month by order

rubenmarin — Tue, 08 Nov 2022 07:37:37 GMT

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.

Re: List month by order

Cassie_L — Tue, 15 Nov 2022 03:50:00 GMT

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')

Re: List month by order

Cassie_L — Thu, 17 Nov 2022 14:54:36 GMT

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,