Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
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
hi,
try below
=sum({$<YYYYMM = {"=$(=floor(MonthEnd(AddMonths(max(Date),-3))))"}>} Amount)
or
=sum({$<YYYYMM = {"=$(=Ddate(AddMonths(AddMonths(max(Date),-3))))"}>} Amount)
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?
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
thank you Rahul,
added Year=,Month= and it works
Sum({<DateKey={">=$(=Num(MonthStart(AddMonths(Max(DATE),-3))))"},Year=,Month=>}AMOUNT)