Rolling 12 month backwards

Mon, 09 Feb 2015 16:09:10 GMT

Hello,

In my expression, i am adding this code,

Count({$<[Cause Code]={'Access Issue'},InciMonth = {">=$(=Month(MonthStart(Max(Approved))-365))<=$(=Month(MonthEnd(Max(Approved))-1))"},InciYear=,InciMonth=>}[Task])

Here i am counting the Task with the Cause Code ='Access Issue'.

Approved is the Date column . InciYear & InciMonth is the Year & Month Respectively.

The values i am getting correctly, but the x-axis is not changing based on my month selection. Can you please help me out.

Thanks,

Sasikumar.R

Re: Rolling 12 month backwards

giakoum — Mon, 09 Feb 2015 16:27:00 GMT

a sample app would help, but I guess you need to create a calculated dimension with similar set analysis

Re: Rolling 12 month backwards

Frank_Hartmann — Mon, 09 Feb 2015 16:43:02 GMT

i think you should create a mastercalendar.

1. Mastercalendar in script:

LET vZeitraum12Monate = '=$' & '(vAktuellerMonat)' & '-11';
LET vZeitraum6Monate = '=$' & '(vAktuellerMonat)' & '-5';
LET vZeitraum3Monate = '=$' & '(vAktuellerMonat)' & '-2';

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 Referal_Date from Mastercalendar with your table, something like:

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

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

2. create diagramm with following dimension:

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

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

3. chart formula (invisible style):

=avg({1}MonthDiff/MonthDiff)
--------------------------------------------------------------------------------------------------------------------

4. Sort: Formula: = MonthNumber

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

5. Your chartexpression: e.g. sum(sales)

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

6. create a Slider with vAktuellerMonat as Variable, Minvalue= -11, Maxvalue= 0 Interval: 1

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

Thats it!

cheers

topic Re: Rolling 12 month backwards in QlikView

Rolling 12 month backwards

Re: Rolling 12 month backwards

Re: Rolling 12 month backwards