Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Rolling 12 month backwards

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

2 Replies
giakoum
Partner - Master II
Partner - Master II

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

Frank_Hartmann
Master II
Master II

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