Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

joeybird
Contributor II

rolling 12 month Qlik Sense

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