Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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