Show last 13 months based on current month

[Anonymous]

Hi. me again.

i have quite a few charts and KPIs but for a particular chart, i want to show the last 13 months vols only - as its currently showing all dates.

Now i have got a master calendar (which i borrowed from elsewhere), which is coded as follows, and i don't really want to mess with it, as its used elsewhere to give me date ranges etc:

MasterCalendar:

Load

TempDate AS Create_Month,

week(TempDate) As Week,

Year(TempDate) As Year,

Month(TempDate) As Month,

Day(TempDate) As Day,

'Q' & ceil(month(TempDate) / 3) AS Quarter,

Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,

WeekDay(TempDate) as WeekDay;

//=== Generate a temp table of dates ===

LOAD

date(mindate + IterNo()) AS TempDate

,maxdate // Used in InYearToDate() above, but not kept

WHILE mindate + IterNo() <= maxdate;

//=== Get min/max dates from Field ===/

LOAD

min(FieldValue('Create_Month', recno()))-1 as mindate,

max(FieldValue('Create_Month', recno())) as maxdate

AUTOGENERATE FieldValueCount('Create_Month');

is there a way to apply a rule to a specific chart to show the last 13 months as an expression or something?

Any advice appreciated

[Anonymous]

Thank you - this worked a treat. although it is reverted the month and year in reverse order and added a column for the total volumes.

This is such a useful forum, i'm learning loads.

[wendytham]

Hi Finn,

After using the below expression, my month sorting is not in ascending order, how to fix this problem?

Please assist. Thank you.

=if(((MakeDate(Year,Month) < MonthStart(Today())) and (MakeDate(Year,Month) >= AddMonths(MonthStart(Today()),-24))),
Month(MakeDate(2000, Month, 1)) & ' ' & Year)