0 Replies Latest reply: May 25, 2015 10:25 AM by Joanna Seldon RSS

    rolling 12 month Qlik Sense

    Joanna Seldon

      Hiya

       

      Qlik Sense:

       

      seen this app / link and have been trying to use the load statement code and help me solve a rolling 12 month issue         

       

      Rolling 12 months sum

       

       

      ...

       

       

      Months:
      mapping LOAD * INLINE [
          Month, MonthN
          Jan, 1
          Feb, 2
          Mar, 3
          Apr, 4
          May, 5
          Jun, 6
          Jul, 7
          Aug, 8
          Sep, 9
          Oct, 10
          Nov, 11
          Dec, 12
      ];


      b:
      Load *,
        Month&' ' &Year as MonthYear,
        MakeDate(Year,ApplyMap('Months',Month),1) as Period
      Resident a;
      Drop table a;

       

      RollingMonths:

      load Period, Period as RollingPeriod, year(Period) as RollingYear Resident b;

      load Period, Addmonths(Period,1,1) as RollingPeriod, year(Addmonths(Period,1,1)) as RollingYear Resident b;

      load Period, Addmonths(Period,2,1) as RollingPeriod, year(Addmonths(Period,2,1)) as RollingYear Resident b;

      load Period, Addmonths(Period,3,1) as RollingPeriod, year(Addmonths(Period,3,1)) as RollingYear Resident b;

      load Period, Addmonths(Period,4,1) as RollingPeriod, year(Addmonths(Period,4,1)) as RollingYear Resident b;

      load Period, Addmonths(Period,5,1) as RollingPeriod, year(Addmonths(Period,5,1)) as RollingYear Resident b;

      load Period, Addmonths(Period,6,1) as RollingPeriod, year(Addmonths(Period,6,1)) as RollingYear Resident b;

      load Period, Addmonths(Period,7,1) as RollingPeriod, year(Addmonths(Period,7,1)) as RollingYear Resident b;

      load Period, Addmonths(Period,8,1) as RollingPeriod, year(Addmonths(Period,8,1)) as RollingYear Resident b;

      load Period, Addmonths(Period,9,1) as RollingPeriod, year(Addmonths(Period,9,1)) as RollingYear Resident b;

      load Period, Addmonths(Period,10,1) as RollingPeriod, year(Addmonths(Period,10,1)) as RollingYear Resident b;

      load Period, Addmonths(Period,11,1) as RollingPeriod, year(Addmonths(Period,11,1)) as RollingYear Resident b; 

       

       

      have have a table being imported via SQL server and its called Orders and a column called OrderDate.. i would like to use the code above to help me create a selection filter that shows a number of months and if you click any of the months it will roll back 12 months e.g "April" it would then be April 30th backwards, all tables and kpi and scatters graphs in the sheets will reflect this.  but i cannot get it to work.


      i have created a time and date calendar


      Calendar: DECLARE FIELD DEFINITION TAGGED '$date' Parameters first_month_of_year = 1 Fields

           Year($1) As Year Tagged '$year',

           Month($1) as Month Tagged '$month',

           Date($1) as Date Tagged ('$date', '$day'),

           Week($1) as Week Tagged '$week',

           Weekday($1) as Weekday Tagged '$weekday',

           DayNumberOfYear($1, first_month_of_year) as DayNumberOfYear Tagged ('$numeric');

       

      DERIVE FIELDS FROM FIELDS [OrderDate] USING Calendar;

       

      Please help

       

      Kind Regards

       

      Joeybird