Set Analysis - Addmonths

[ilanbaruch]

Hi all

i have an expression with AddMonths in Set Analysis (below)

working fine with Today() as date but when trying to connect to model Date Field both expressions retrieve same value

any ideas?

SUM({<YYYYMM={"$(='>=' & date(AddMonths(Max(DATE),-3),'YYYYMM'))"}>}AMOUNT)

//SUM({<YYYYMM={"$(='>=' & date(AddMonths(Max(DATE),-1),'YYYYMM'))"}>}AMOUNT)

[Chanty4u]

hi,

try below

=sum({$<YYYYMM = {"=$(=floor(MonthEnd(AddMonths(max(Date),-3))))"}>} Amount)

or

=sum({$<YYYYMM = {"=$(=Ddate(AddMonths(AddMonths(max(Date),-3))))"}>} Amount)

[Anil_Babu_Samineni]

SUM({<YYYYMM={"$(='>=' & date(AddMonths(Max(DATE),-3),'YYYYMM'))"}>}AMOUNT)

//SUM({<YYYYMM={"$(='>=' & date(AddMonths(Max(DATE),-1),'YYYYMM'))"}>}AMOUNT)

Here, YYYYMM is the Field Name? You cannot call directly with the Format

May be you may need FieldName instead of YYYYMM

I doubt, How Today() works here if YYYYMM is not a field?

[rahulpawarb · Accepted solution]

Hello Ilan,

Please try below given sample expression:

Sum({<DateKey={">=$(=Num(MonthStart(AddMonths(Max(DATE),-3))))"}>}AMOUNT)

P.S.: In this example, DateKey field should be numeric date field i.e. Floor(DATE) AS DateKey

Regards!

Rahul

[ilanbaruch]

thank you Rahul,

added Year=,Month= and it works

Sum({<DateKey={">=$(=Num(MonthStart(AddMonths(Max(DATE),-3))))"},Year=,Month=>}AMOUNT)