Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Live chat with experts, bring your API Integration questions. June 15th, 10 AM ET. REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
brucejensen
Contributor III
Contributor III

Rolling 12 months in Chart

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

13 Replies
Frank_Hartmann
Master II
Master II

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
Contributor III
Contributor III
Author

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
MVP
MVP

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

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

brucejensen
Contributor III
Contributor III
Author

Ok. It's working now. Thanks again!