Question on Date Formats

[stuwannop]

Hi Everyone

I want to create a chart using a date field as a dimension. I want to group it by Month/Year and therefore want the format to be 'MMM-YY'. This I can do using the Date() function in the script. Problem is when I make a chart the dates don't group automatically into MMM - YY they just repeat themselves. How can I get them grouped?

Bear in mind also that this particular date I'm using isn't the only date in the table. This means that I can't really write a calendar script as I'm already using one.

Thanks

Stu

[Gysbert_Wassenaar · Accepted solution]

Formatting a date will only change the display format the date. The underlying value is still the date. You'll want to create a new field. Something like date(monthstart(MyDate),'MMM-YY') as MonthYear

[Sokkorn]

Hi Stu,

In load script you can try with:

1. Text(Date(Field,'MMM-YY')) AS [MonthYear]

2. Or Month(Field) &'-'&Year(Field) AS [MonthYear]

Regards,

Sokkorn

[stuwannop]

Thanks to both of you for that.

I have used something similar to Sokkorns solution in my master calendar script and I ran into a couple of problems with the sorting - I couldn't sort them in the order I wanted - my formula was:

Month(Date)&' '&right(year(Date),4) as Dates_MonthYear

Yet Gysbert your solution seems to sort the dates old to new without any problem at all. Are these two formulas stored differently?

Stu

[hic]

Gysbert's solution is by far the best one. The two formulae are indeed stored differently. By using the Date(MonthStart()) combination you will keep it in a dual format (both date number and date format) so they will be sorted automagically.

Avoid all string operations when creating time/date fields - if possible.

HIC

[stuwannop]

Thanks Henric I understand - my "master calendar" script contains all sorts of strings in it. I largely get away with it by sorting by load order (I tend to use dates from sales tables to generate the dates) but this ensures I don't need to worry about that anymore.

Many thanks.

Stu