2 Replies Latest reply: Mar 20, 2017 3:08 PM by Massimo Grossi RSS

    Track items that have not been run in 90 days

    Brenda Degele

      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

        • Re: Track items that have not been run in 90 days
          Aar Kay

          Try this

           

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

          • Re: Track items that have not been run in 90 days
            Massimo Grossi

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