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

    Rolling 12 month backwards

    Sasi Kumar

      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
          ioannis giakoumakis

          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

            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