Date Comparison - SET ANALYSIS

[Anonymous]

Hi Guys,

I am using this below code to view DealId count for Current Year and Previous Year

Current Month:

=count({<MONTH=, Year={"=Date(max(Date),'YYYY')"}>} DealID) -- Works for Current

Previous Month:

=count({<MONTH=, Year={"=Date(AddYears(max(Date),-1),'YYYY')"}>} DealID) -- Does not work for previous.

Code works fine for Current Month. To get Previous Month I use AddYears(Max(Date),-1) code to fetch previous year.

Unfortunately Previous Month code does not work and give me Current Month Data.instead.....

Can somebody help me out with this.....AddYears stuff...

Regards,

Shree Angane

[Gysbert_Wassenaar]

Year={"=Date(AddYears(max(Date),-1),'YYYY')"} selects the same month, but a year earlier. If you want the previous month you need to use the AddMonths function instead.

[Anonymous]

Apologies for Typo ... I actually meant

Current Year:

=count({<MONTH=, Year={"=Date(max(Date),'YYYY')"}>} DealID) -- Works for Current

Previous Year:

=count({<MONTH=, Year={"=Date(AddYears(max(Date),-1),'YYYY')"}>} DealID) -- Does not work for previous.

I get same output for both of these expressions

[Gysbert_Wassenaar · Accepted solution]

If you make a selection in the Date field you also need to 'reset' that selection in the set modifier:

=count({<Date=, MONTH=, Year={"=Date(AddYears(max(Date),-1),'YYYY')"}>} DealID)

And of course your Year field should be a date field since you're comparing it in the set modifier with a date, one that's formatted as a year, but still a date. If your Year field contains year values instead of dates then you need to use something like Year={"$(=Year(AddYears(max(Date),-1)))"}

[Anonymous]

Gysbert.....you are fabulous ..... solution worked.....

Best Regards,

Shree Angane