Master Calendar - Natural Language

Anonymous — Mon, 19 Feb 2018 18:06:25 GMT

Hello all.

I am working on a prototype of a dashboard and I wanted to load a master calendar to help with the time dimension.

I got this form an QlikView demo and found a modification to adapt to our fiscal quarters (Q1 = Oct - Dec).

What I would like to do is be able to provide dimensions using more natural language such as "This week", "Last Week", "This Month", "Last Month", Etc.. for Q and year.

I wanted to provide expressions to evaluate week over week, month over month, etc..

I made an alteration to the master calendar script to get a This Week and Last Week using an If statement. But it only worked where the date matched the week ending and for the previous week I had to subtract 7 to get the right date.

It did work but I'm concerned this will not be an efficient method to write in these dimensions for all the weeks.

The data is structured by week ending (which is always a saturday) beginning in May of 17.

Any help is appreciated.

Thanks!

Nate

QuartersMap:

MAPPING LOAD

rowno() as Month,

'Q' & if (Ceil (rowno()/3) = 4, 1, Ceil (rowno()/3) + 1) as Quarter

AUTOGENERATE (12);

Temp:

Load

min(weekEnding) as minDate,

max(weekEnding) as maxDate

Resident driverMetrics;

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

DROP Table Temp;

TempCalendar:

LOAD

$(varMinDate) + Iterno()-1 As Num,

Date($(varMinDate) + IterNo() - 1) as TempDate

AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

MasterCalendar:

Load

TempDate AS weekEnding,

week(TempDate) As Week,

Year(TempDate) As Year,

Month(TempDate) As Month,

Day(TempDate) As Day,

YeartoDate(TempDate)*-1 as CurYTDFlag,

YeartoDate(TempDate,-1)*-1 as LastYTDFlag,

inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,

date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,

ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,

Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,

WeekDay(TempDate) as WeekDay,

if(TempDate=Date($(varMaxDate)), 'w0', if(TempDate=Date($(varMaxDate)-7), 'w-1')) as testWeek

//Month(AddMonths(TempDate,-1)) as prevMonth

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

topic Master Calendar - Natural Language in QlikView

Master Calendar - Natural Language