Rolling 12 months in Chart

[brucejensen]

Can someone tell me how to get my chart to only display a rolling 12 month period? I have a master calendar but I can't get the set analysis to work. I'm not sure which pieces of the master calendar I should be using to come up with the rolling 12 mos. My date field is called [Month Reviewed] i.e. Mar-15 for March 2015.

Any help would be greatly appreciated

[Frank_Hartmann]

1. Mastercalendar in script:

LET vZeitraum12Monate = '=$' & '(vAktuellerMonat)' & '-11';

LET vDateMin = Num(MakeDate(2008,1,1));

LET vDateMax = Floor(YearEnd(AddMonths(Today(), 1)));

LET vDateToday = Num(Today());

TempCalendar: 

LOAD

$(vDateMin) + RowNo() - 1 AS DateNumber, 

Date($(vDateMin) + RowNo() - 1) AS TempDate

AUTOGENERATE 1 

WHILE $(vDateMin)+IterNo()-1<= $(vDateMax); 

MasterCalendar: 

LOAD

(year(TempDate)*12 + Month(TempDate)) - (year($(vDateMin))*12 + Month($(vDateMin))) as MonthNumber,

(year(TempDate)*12 + Month(TempDate)) - (year($(vDateToday))*12 + Month($(vDateToday))) as MonthDiff,

'AK_' & Date(TempDate,'YYYYMMDD') AS Referal_Date, 

Day(TempDate) AS CalendarDay, 

WeekDay(TempDate) AS CalendarWeekDay, 

Week(TempDate) AS CalendarWeek, 

Month(TempDate) AS CalendarMonth, 

Num(Month(TempDate),00) AS MonthAsNumber,

Year(TempDate) AS CalendarYear, 

'Q' & Ceil(Month(TempDate)/3) AS CalendarQuarter,

WeekDay(TempDate) & '-' & Year(TempDate) AS CalendarWeekAndYear, 

Date(TempDate,'YYYY-MM') AS CalendarMonthAndYear,

Num(Date(TempDate,'YYYYMMDD')) AS CalendarYearMonthDay,

Year(TempDate)&Num(Month(TempDate),00) AS CalendarYearMonth,

Num(Month(TempDate),00)&'/'&Date(TempDate,'YY') AS DateDimension,

Year2Date(TempDate, 0, 1, $(vToday))*-1 AS CalendarCurYTDFlag,

Year2Date(TempDate,-1, 1, $(vToday))*-1 AS CalendarLastYTDFlag

RESIDENT TempCalendar ORDER BY TempDate ASC; 

DROP TABLE TempCalendar;

--------------------------------------------------------------------------------------------------------------------

connect the Referal_Date field from Mastercalendar with your table, something like:

   'AK_' & Date(YourDatefield,'YYYYMMDD') AS Referal_Date    or

   'AK_' & Date(Date#(YourDatefield, 'YYYY/MM'),'YYYYMMDD') AS Referal_Date,

corresponding to the format of your datefield.

--------------------------------------------------------------------------------------------

2. create a diagramm with following dimension:     

            =if(MonthDiff>=$(vZeitraum12Monate) and MonthDiff<=$(vAktuellerMonat),MonthAsNumber)

--------------------------------------------------------------------------------------------

3. First: chart expression (hidden):

            =avg({1}MonthDiff/MonthDiff)

   Second: Your Chartexpression: e.g. sum(sales)

--------------------------------------------------------------------------------------------

4. Sort: Formula: MonthNumber

--------------------------------------------------------------------------------------------

5. create slider with vAktuellerMonat as Variable, Minvalue= -11, Maxvalue= 0 Interval: 1

--------------------------------------------------------------------------------------------

hope it helps!

[brucejensen]

Quick question - I'm having trouble getting this to reload properly. Here is my script and the script for the Master Calendar. Do you see any changes I need to make?

MasterListReviewedLocationsData:
LOAD Date(MonthStart(TempDate),'MMM-YY') as [Month Reviewed],
Area,
[Facility Code],
[Building Name],
[Owned/Leased],
[Lease Expiration Date],
[Building Use],
TCOC,
RSF,
[Occupancy Count/Capacity],
[Next Notification Date to Renew],
[Notificaiton Date to Terminate],
[Renewal Term],
Address,
City,
State,
WMS,
[Future FCC Location],
[Necessary for Catastrophe],
[Final Recommendation],
[Facility Status],
[Status Notes],
Reviewed,
[Renewed - Remain Open],
Buyout,
[Reduce Space],
Close,
[Reduction in TCOc],
[Buyout Cost],
[RSF Reduction]
FROM
[2015 Lease Status - March.xlsm]
(ooxml, embedded labels, table is [Master List-Reviewed Locations]);

Master Calendar script...

QuartersMap: 
MAPPING LOAD  
rowno() as Month, 
'Q' & Ceil (rowno()/3) as Quarter 
AUTOGENERATE (12); 

Temp: 
Load 
min([Month Reviewed]) as minDate, 
max([Month Reviewed]) as maxDate 
Resident MasterListReviewedLocationsData; 

Let varMinDate = Num(Peek('minDate', 0, 'Temp')); 
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp')); 
DROP Table Temp; 

TempCalendar: 
LOAD 
$(varMinDate) + Iterno()-1 As Num, 
Date($(varMinDate) + IterNo() - 1) as TempDate 
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 

MasterCalendar: 
Load 
TempDate AS [Month Reviewed], 
week(TempDate) As Week, 
Year(TempDate) As Year, 
Month(TempDate) As Month, 
Day(TempDate) As Day, 
YeartoDate(TempDate)*-1 as CurYTDFlag, 
YeartoDate(TempDate,-1)*-1 as LastYTDFlag, 
inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12, 
date(monthstart(TempDate), 'MMM-YYYY') as MonthYear, 
ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter, 
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear, 
WeekDay(TempDate) as WeekDay 
Resident TempCalendar 
Order By TempDate ASC; 
Drop Table TempCalendar; 

[MK_QSL]

Date(MonthStart(TempDate),'MMM-YY') as [Month Reviewed] ,

You have to use in Master Calendar.... not in the fact table..

[brucejensen]

Ok. It's working now. Thanks again!