Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello community,
I would like to create a filter pane that includes the following time period entries so that th data is shown for the selected period:
E.g.
last 12 month
last 9 month
last 6 month
last 12 weeks
last 8 weeks
last 4 weeks
last 14 days
how can I create this ?
Thanks in advance,
Mathias
Hello Mathias,
you can use this script as MasterCalender
//Generate calendar range based on the Claendar start and end variables. All date within the range are genrated.
//usually you would use your min/max from desired Table Dates here
LET vCalendarStart = Date('2015-01-01'); // Static start
LET vCalendarEnd = Date(today()); // Today
LET vCalendarLength = Num(Date#(vCalendarEnd)) - Num(Date#(vCalendarStart)) + 1;
Calendar:
LOAD
*,
If(Date > Date(MonthStart('$(vCalendarEnd)', -7)) and Date <= Date('$(vCalendarEnd)'), 1,0) as Last6Months,
If(Date > Date(MonthStart('$(vCalendarEnd)', -10)) and Date <= Date('$(vCalendarEnd)'), 1,0) as Last9Months,
If(Date > Date(MonthStart('$(vCalendarEnd)', -13)) and Date <= Date('$(vCalendarEnd)'), 1,0) as Last12Months,
If(Date > Date(MonthStart('$(vCalendarEnd)', -4)) and Date <= Date('$(vCalendarEnd)'), 1,0) as Last12Weeks,
If(Date > Date(MonthStart('$(vCalendarEnd)', -3)) and Date <= Date('$(vCalendarEnd)'), 1,0) as Last8Weeks,
If(Date > Date(MonthStart('$(vCalendarEnd)', -2)) and Date <= Date('$(vCalendarEnd)'), 1,0) as Last4Weeks,
If(Date > Date('$(vCalendarEnd)'-14) and Date <= Date('$(vCalendarEnd)'), 1,0) as Last14Days
;
LOAD
*,
Num(Ceil(Month/3), 'Q0') AS Quarter,
WeekDay(Date) AS WeekDay,
Date(MonthStart(Date), 'YYYY MMM') AS YearMonth,
WeekName(Date) AS YearWeek,
Date#(Date(Date, 'MMMM'), 'MMMM') AS MonthName //Month according to MonthLongNames definition
;
LOAD
Date,
Year(Date) AS Year,
Num(Month(Date), '00') AS Month,
Num(Day(Date), '00') AS Day,
Num(Week(Date), '00') AS Week
;
LOAD
Date($(#vCalendarStart) + RecNo()-1) AS Date
AutoGenerate $(#vCalendarLength);
See Attachment for QVW
Regards
Tim
Hi Tim,
many thanks. I will try your suggestion.
all the best from Munich
Mathias
Thanks Tim, this is exactly what i was looking for.