Date comparison with addmonths in SET analysis

KirDeg — Thu, 11 Mar 2021 21:56:26 GMT

Good day experts,

I am trying to make a filter of client's activity based on their transactions for the 2 last month from reporting date (vReportingDate). I managed to write it without using a variable and it works fine:

IF(AGGR(SUM({$<Day = {">=01"}, Month = {">=11"}, Year = {">=2020"}>}[#Sum]), %IDClient)>0, 'active', 'inactive')

But it is not working, when I am trying to implement the same filter with variable:

IF(AGGR(SUM({$<Day = {">=$(=Day(addmonths('$(vReportingDate)', -2)))"},
Month = {">=$(=Month(addmonths('$(vReportingDate)', -2)))"},
Year = {">=$(=Year(addmonths('$(vReportingDate)', -2)))"}>}[#Sum]), %IDClient)>0, 'active', 'inactive')

It gives a wrong answer for vReportingDate = 01.01.2021

I think it happens because of misuse date comparison, but I can't detect an error. I am very grateful for any assistance.

Re: Date comparison with addmonths in SET analysis

MayilVahanan — Fri, 12 Mar 2021 03:42:45 GMT

Hi @KirDeg

Instead of use Day, Month & Year, try with Date like below

IF(AGGR(SUM({$<DateField= {">=$(=Date(MonthStart('$(vReportingDate)',-2),'YourDateFormat'))<=$(=Date('$(vReportingDate)','YourDateFormat'))"}>}[#Sum]), %IDClient)>0, 'active', 'inactive')

topic Date comparison with addmonths in SET analysis in QlikView

Date comparison with addmonths in SET analysis

Re: Date comparison with addmonths in SET analysis