Sorting of Date field

[Anonymous]

Hi..

I have a date field named ProjectMonth. Data in this field is as below Aug-2015, Jul-2015 etc.

When I try to plot this field on the X axis these dont appear in the sorted fashion. I am guessing the reason may be that it is being stored as Text so I used num#(ProjectMonth) but I am still not able to sort the months in ascending order.

Can anybody please help or suggest a way out?

[Michiel_QV_Fan]

Make the format date(date#(yourdatefield, 'MMM-YYYY'), 'MMM-YYYY')

[#3]

Hi,

Try to order by 'Expression' > num#(ProjectMonth)

[Anonymous]

the values just disappear from the X axis.

[rubenmarin]

Hi Sanjyot, the expression proposed by Michiel should work, can you upload a sample to check where is the issue?

date(date#(ProjectMonth, 'MMM-YYYY'), 'MMM-YYYY')

Maybe the MonthNames variable doesn't have the english names for months.

[Anonymous]

Hi

I have attached the qvw file and base file that I have created.

I have tried the format suggested by Michiel but it somehow doest seem to work. Can you please assist?

[sunny_talwar · Accepted solution]

Use the following sorting expression:

MonthName(Date#(ProjectMonth, 'MMM-YYYY'))

[rubenmarin]

Hi, check the first chart, I changed the dimension to:

=date(date#(ProjectMonth, 'MMM-YYYY'), 'MMM-YYYY')

and checked 'Numeric' in sort tab (this doesn't have any use but is more consistent to what you need (dates are numbers))

[sunny_talwar]

Or you can change your script so that ProjectDate is treated as a datefield:

LOAD PlanType,

    [BF Level 4 Descr],

    [Global Function],

    Country,

    [Cost Centre ID],

    [GPDM Team],

    MonthName(Date#(ProjectMonth, 'MMM-YYYY')) as ProjectMonth,

    HCM

FROM

(ooxml, embedded labels, table is _qSupplyVsDemandVsActualsVsAllo);

[daniellial]

I tested the calculated dimension

=Date#(ProjectMonth, 'MMM-YYYY')

and it's ok!