According to your definition of assigning CalendarWeekEndingFriDate to months, I think you just need to create the month from these dates. For example, you can create a calculated dimension
=monthname( CalendarWeekEndingFriDate )
and then use as expression something like
If this results in the expected values, I would recommend adding a new field that calculates the months from your date field in the load script
monthname(CalendarWeekEndingFriDate) as YearMonth,
Hope this helps,
edit: and to aggregate to year level, just use year() function.
Awesome, thanks very much Stefan and Jagan,
Stefan using your suggestion I can do a monthly chart dimension, and you're right Jagan to actually get a value to plot I need to use your aggr formula syntax.
Given my input data I now get as output this, which is great.
Hmmm, but now this brings one more question.
LOAD MonthName(CalendarWeekEndingFriDate) as CalendarMonthYear
as the scripted dimension
as the expression I get the above result.
As you can see I am getting no dimension summarised values output for April 2012.
Am guessing its because the month is still incomplete / presence of null values, but how can I handle this aspect so that for incomplete periods such as April 2012 now, I can get a "MonthToDate" value to plot?
Stop Press. Ignore the above. It actually works fine - also for April. When I tested with just one ProductionColumn, I'm sure I didnt have any date filters active and it wouldnt show me April data hence I was confused. I just added further production columns to the chart using the same aggr expression and April has appeared, so thanks, everything working 100% now.
Message was edited by: manoangazi