Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All
I'm brand new to Qlik Sense...I have a list of reports with their last run date/time. I need to be able to list those reports that have not run in the last 90 days. The following are the formulas that I've tried to use for the LAST_RUN_TIME field:
date(date#('LAST_RUN_TIME', 'YYYYMMDDhhmmss') - 90, 'MM/DD/YYYY hh:mmTT')
timestamp(timestamp#(date(LAST_RUN_TIME-90, 'YYYYMMDDhhmmss'),'MM/DD/YYYY hh:mmTT'))
Any help would be greatly appreciated.
Brenda
Try this
Timestamp(TimeStamp#('20170101224401', 'YYYYMMDDhhmmss')-90,'MM/DD/YYYY hh:mmTT')
// make some test data, replace with your data
X:
LOAD
'Report ' & Ceil(Rand()*20) as REPORT,
timestamp(makedate(2016) + rand() + rand()*(365+80), 'YYYYMMDDhhmmss') as LAST_RUN_TIME
AutoGenerate 100;
// flag 1 for report run in the last 90 days, 0 not run in the last 90 days
Y:
NoConcatenate LOAD
REPORT,
LAST_RUN_TIME,
date(floor(timestamp#(LAST_RUN_TIME, 'YYYYMMDDhhmmss'))) as LAST_RUN_DATE,
-1 * (floor(timestamp#(LAST_RUN_TIME, 'YYYYMMDDhhmmss')) >= (Today() -90)) as FLAG_LAST_90
Resident X;
// add the flag to X table
Left Join (X) LOAD
REPORT,
max(LAST_RUN_DATE) as LAST_RUN_DATE,
max(FLAG_LAST_90) as FLAG_LAST_90
RESIDENT Y
GROUP BY REPORT;
DROP Table Y;