Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
KirDeg
Contributor
Contributor

Date comparison with addmonths in SET analysis

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. 

1 Reply
MayilVahanan

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

Thanks & Regards,
Mayil Vahanan R