2 Replies Latest reply: Feb 9, 2015 12:30 PM by Frank Hartmann

# Rolling 12 month backwards

Hello,

In my expression, i am adding this code,

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

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

• ###### Re: Rolling 12 month backwards

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 vDateToday = Num(Today());

TempCalendar:
\$(vDateMin) + RowNo() - 1 AS DateNumber,
Date(\$(vDateMin) + RowNo() - 1) AS TempDate
AUTOGENERATE 1
WHILE \$(vDateMin)+IterNo()-1<= \$(vDateMax);

MasterCalendar:
(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

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

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

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

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

Thats it!

cheers