Qlik Community

Qlik Sense Enterprise Ideas

Ideas for Qlik Sense Enterprise.

Identifying last n working days in script via master calendar

Partner
Partner

Identifying last n working days in script via master calendar

I was working to identify records with dates in last 5 working days (From max date in transactions).  This logic could then simplify set-analysis and enable a faster end-user experience.  Consider adding this in your calendar script:  if(TempDate>= Date(FirstWorkDate('$(vMaxDate)',5)), 1,0) as LastFiveWorkDays,

 

Temp:
Load
min(startdate) as minDate,
max(startdate) as maxDate
Resident Transactions;


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

DROP Table Temp;

TempCalendar:
LOAD distinct
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While '$(varMinDate)' + IterNo() -1 <= '$(varMaxDate)';


MasterCalendar:
Load
TempDate as startdate,
if(TempDate>= Date(FirstWorkDate('$(vMaxDate)',5)), 1,0) as LastFiveWorkDays,
Date('$(vMaxDate)') as MaxDate
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;

Sandeep Sarawgi