3 Replies Latest reply: Jan 23, 2017 8:37 AM by Sunny Talwar RSS

    Last X Periods from Transformed Field

    Dave Melillo

      I am trying to just get the latest 3 months/quarters/years to display in a chart. The set analysis I am using is below

       

      = avg({< [Lead Stage] = {'Prospect','Suspect',''}, MonthName([Lead Create Date]) = {'>= Max(MonthName([Lead Create Date]) -3' }>}Today() - [Lead Create Date])

       

      It seems like the issue is that I am using the MonthName() function. Does anyone have any hints on how to handle this?

       

      Thank you,

       

      - dave

        • Re: Last X Periods from Transformed Field
          Sunny Talwar

          Yes you are right. QlikView's expression doesn't allow for you to use functions on the LHS of a set modifier..... You can try like this:

           

          =Avg({<[Lead Stage] = {'Prospect','Suspect',''}, [Lead Create Date] = {"=MonthName([Lead Create Date]) >= MonthName(AddMonths(Max(TOTAL [Lead Create Date]), -3))"}>}Today() - [Lead Create Date])

           

          or create a new field in the script (avoid using MonthName and use MonthStart with date format)

           

          LOAD [Lead Create Date],

                    Date(MonthStart([Lead Create Date]), 'MMM-YYYY')) as MonthYear

                    ...

          FROM ....;

           

          and then this

          =Avg({<[Lead Stage] = {'Prospect','Suspect',''}, MonthYear = {"$(='>=' & Date(MonthStart(Max(MonthYear), -3), 'MMM-YYYY'))"}>}Today() - [Lead Create Date])