Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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)