Hi all,
My master calendar and AsOf table is not returning any data. The syntax is the same as that from another app that I have created an AsOf table on and both the AsOf table and master calendar is perfectly fine in that other app. The only thing I have done differently in this app is convert text to date whereas the working app the date was already date and was not converted.
Can anyone spot anything wrong with my code?
SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='£#,##0.00;-£#,##0.00';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';
SET FirstWeekDay=0;
SET BrokenWeeks=0;
SET ReferenceDay=4;
SET FirstMonthOfYear=1;
SET CollationLocale='en-GB';
SET CreateSearchIndexOnReload=1;
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';
SET NumericalAbbreviation='3:k;6:M;9:G;12:T;15:P;18:E;21:Z;24:Y;-3:m;-6:μ;-9:n;-12:p;-15:f;-18:a;-21:z;-24:y';
SheetOne:
LOAD
"Qlik Sense Apps",
Stream,
timestamp("Last Reload") as "LastReload",
"Session Duration in Minutes",
Date(Floor(Date#([LastUsed], 'MMM-YY')), 'MMM-YYYY') as LastUsed
FROM [lib://AttachedFiles/For Graham(apps).xlsx]
(ooxml, embedded labels, table is Sheet1);
SheetTwo:
Load
"How many users have access to stream"
FROM [lib://AttachedFiles/For Graham(apps).xlsx]
(ooxml, embedded labels, table is Sheet2);
QuartersMap:
MAPPING Load
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter //q1, 2, 3, and 4
AutoGenerate(12);
Temp:
Load
min(LastUsed) as minDate,
max(LastUsed) as maxDate //takes minium and max date
Resident SheetOne;
SET varMinDate = num(Peek('minDate', 0, 'Temp'));
SET varMaxDate = num(Peek('maxDate', 0, 'Temp')); // fills in the gap
DROP Table Temp;
TempCalendar:
LOAD
$(varMinDate) + Iterno() -1 As Num,
Date($(varMinDate) + Iterno() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + Iterno() - 1 <= $(varMaxDate); // populates a table with the above
MasterCalendar:
Load
TempDate AS LastUsed,
week(TempDate) As Week,
year(TempDate) AS Year,
Month(TempDate) AS Month,
Day(TempDate) AS Day,
ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear, //creates a calandar with the above table also matching to q1, q2, q3 etc
WeekDay(TempDate) as WeekDay
Resident TempCalendar
Order by TempDate ASC;
DROP TABLE TempCalendar;
// ======== Create a list of distinct Months ========
tmpAsOfCalendar:
Load distinct Month
Resident [MasterCalendar] ;
// ======== Cartesian product with itself ========
Join (tmpAsOfCalendar)
Load Month as AsOfMonth
Resident tmpAsOfCalendar ;
// ======== Reload, filter and calculate additional fields ========
[As-Of Calendar]:
Load Month,
AsOfMonth,
Round((AsOfMonth-Month)*12/365.2425) as MonthDiff,
Year(AsOfMonth)-Year(Month) as YearDiff
Resident tmpAsOfCalendar
Where AsOfMonth >= Month;
Drop Table tmpAsOfCalendar;
SET YearlyAccumulationSession = 'Sum({$<YearDiff={0}>} SessionMinutes <> NULL)';
SET SixMonthRollingAverageSession = 'Sum({$<MonthDiff={"<6"}>} (SessionMinutes <> NULL)) / Count(distinct {$<MonthDiff={"<6"}>} Month)';
SET IsSameMonthSession = 'MonthDiff(0,1,0)';
SET IsSameYearSession = 'YearDiff(0,1,0)';
SET RollingSixSession = 'MonthDiff(<6,1,0)';
SET NonAccumulationSession = 'Sum({$<MonthDiff={0}>} SessionMinutes <> NULL)';
Your help is appreciated. Many thanks.
