Track items that have not been run in 90 days

[Anonymous]

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

[aarkay29]

Try this

Timestamp(TimeStamp#('20170101224401', 'YYYYMMDDhhmmss')-90,'MM/DD/YYYY hh:mmTT')

[maxgro]

// 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;