Last X Periods from Transformed Field

[Anonymous]

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

[sunny_talwar · Accepted solution]

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])

[Anonymous]

Amazing Sunny! Thank you. I went with the first option so I didn't have to reload my data model. Do you think I will have major performance issues moving forward?

[sunny_talwar]

I don't think so... but I am not 100% confident.