Date dimension formatting

[Anonymous]

Hi,

I have a column in my table called MonthYear which contains values like JUL-2015, AUG-2015 ..... JUN-2016.

July is the first fiscal month and June is the last fiscal month.  When I used the "MonthYear" as a dimension in my Line Chart

on the x-axis, I need the fiscal month to come out in the correct chronological order as listed in above.  According to

QV Help, there is a DUAL() function which takes the form of DUAL(character, integer) format.  The integer portion will be used

as a sorting value.  This function does not work well when my MonthYear contains both character and number data.  I am not

able to get this dimension to come out the order I need it.  It works perfectly when it's all character data as the first argument. 

Does anyone have any idea or an alternative approach to solve my problem?

Thanks

[rubenmarin]

Hy Sydney, when using dual() the first part is text to show, the second part is the number related to that text, you can create the months like:

Dual(Date(DateField, 'MMM-YYYY'), Year(DateField)*12+Month(DateField)) as MonthYear

[Anonymous]

Hi Ruben,

Thanks for the quick response.

My values are actually JUL-15, AUG-15 and etc.  So I used date format as "MMM-YY".  When I ran it, it showed a dash or hypen.  Then I tried to run the first argument to see what I get as in:

Date(CalYearMonthName, 'MMM-YY') as 'CalYearMonthName2'

This is what I see in the data model.

Am I missing something here?

[sunny_talwar]

Try this:

Date#(CalYearMonthName, 'MMM-YY') as CalYearMonthName2

[rubenmarin]

Nothing is missing, I supossed MonthYear field was created by you based on a date field, if it's not created by you follow Sunny answer to transform it into a real date.

[Anonymous]

Sunny,

Your trick fixed the first argument.  But still getting dash/hypen when ran it as

Dual(Date#(CalYearMonthName, 'MMM-YY'), Year(CalYearMonthName)*12+Month(CalYearMonthName)) as 'CalYearMonthName2'

[sunny_talwar]

I guess the whole things will need the same Date#() function

Dual(Date#(CalYearMonthName, 'MMM-YY'), Year(Date#(CalYearMonthName, 'MMM-YY'))*12+Month(Date#(CalYearMonthName, 'MMM-YY'))) as 'CalYearMonthName2'

[Anonymous · Accepted solution]

You guys are awsome!!!

It's working beautifully.  I am going to test it more with more data.

[rupamjyotidas]

Can you close the thread