Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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])
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])
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?
I don't think so... but I am not 100% confident.