topic Re: Rolling 12 months in Chart in QlikView

Rolling 12 months in Chart

brucejensen — Thu, 16 Apr 2015 14:34:45 GMT

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

Re: Rolling 12 months in Chart

awhitfield — Thu, 16 Apr 2015 14:51:43 GMT

Hi Bruce,

You could use Dimension limits against your date field, e.g. tick Show only = 12, un-tick show others

Regards

Andy

Re: Rolling 12 months in Chart

brucejensen — Thu, 16 Apr 2015 15:13:10 GMT

Thanks. I did that and for some reason the chart keeps Jan-14, Feb-14, and Mar-14 and drops Jan-15 and Feb-15?

Re: Rolling 12 months in Chart

awhitfield — Thu, 16 Apr 2015 15:22:45 GMT

Interesting, may be a sort order issue Sep-14 is missing 2 in both screen shots. Any chance you can post the qvw?

Andy

Re: Rolling 12 months in Chart

MK_QSL — Thu, 16 Apr 2015 15:26:52 GMT

Change according to your FieldName from my below example...

Create below field in your script..

Date(MonthStart(TempDate),'MMM-YY') as InvoiceMonthYear,

Last 12 Months Sales From Current Month

Create a Bar Chart

Dimension

InvoiceMonthYear

Expression

=SUM({<

InvoiceDate=,InvoiceDay=,InvoiceMonth=,InvoiceQuarter=,InvoiceWeekDay=,InvoiceYear=,

InvoiceMonthYear = {">=$(=Date(MonthStart(Today(),-11),'MMM-YY'))<=$(=Date(MonthStart(Today()),'MMM-YY'))"}

>}Sales)

Last 12 Months Sales From User Selected MonthYear

Create a Bar Chart

Dimension

InvoiceMonthYear

Expression

=SUM({<

InvoiceDate=,InvoiceDay=,InvoiceMonth=,InvoiceQuarter=,InvoiceWeekDay=,InvoiceYear=,

InvoiceMonthYear = {">=$(=Date(MonthStart(Max(InvoiceMonthYear),-11),'MMM-YY'))<=$(=Date(MonthStart(Max(InvoiceMonthYear)),'MMM-YY'))"}

>}Sales)

Re: Rolling 12 months in Chart

brucejensen — Thu, 16 Apr 2015 15:27:33 GMT

Actually, we don't have any Sep-14 data so that's why that month is missing. And, no unfortunately I can't post the .qvw..

Re: Rolling 12 months in Chart

nagaiank — Thu, 16 Apr 2015 15:31:35 GMT

A syntax for rolling 12 month set expression is in the blog The Magic of Set Analysis – Point In Time Reporting | iQlik - Everything QlikView.

Hope this helps.

Re: Rolling 12 months in Chart

awhitfield — Thu, 16 Apr 2015 15:35:13 GMT

1. How are you sorting the axis?

2. Can you post the Calendar Script?

Andy

Re: Rolling 12 months in Chart

awhitfield — Thu, 16 Apr 2015 15:47:13 GMT

Arggh, just re read what I put earlier - total garbage!

Instead, how a looks at the attached link which does the job properly

https://community.qlik.com/message/398262#398262

Humbly!

Andy

Re: Rolling 12 months in Chart

brucejensen — Thu, 16 Apr 2015 15:49:08 GMT

Works perfectly. Thank you so much!

Re: Rolling 12 months in Chart

Frank_Hartmann — Thu, 16 Apr 2015 15:49:43 GMT

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!

Re: Rolling 12 months in Chart

brucejensen — Thu, 16 Apr 2015 16:06:53 GMT

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;

Re: Rolling 12 months in Chart

MK_QSL — Thu, 16 Apr 2015 16:09:32 GMT

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

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

Re: Rolling 12 months in Chart

brucejensen — Thu, 16 Apr 2015 16:18:18 GMT

Ok. It's working now. Thanks again!