topic Last X Periods from Transformed Field in QlikView

Last X Periods from Transformed Field

Anonymous — Sun, 22 Jan 2017 23:57:41 GMT

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 — Mon, 23 Jan 2017 02:06:02 GMT

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

Re: Last X Periods from Transformed Field

Anonymous — Mon, 23 Jan 2017 13:35:49 GMT

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?

Re: Last X Periods from Transformed Field

sunny_talwar — Mon, 23 Jan 2017 13:37:56 GMT

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