rolling 12 month Qlik Sense

joeybird — Mon, 25 May 2015 14:25:11 GMT

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

...

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

topic rolling 12 month Qlik Sense in App Development

rolling 12 month Qlik Sense