Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
ilanbaruch
Specialist
Specialist

Set Analysis - Addmonths

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)

1 Solution

Accepted Solutions
rahulpawarb
Specialist III
Specialist III

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

View solution in original post

4 Replies
Chanty4u
MVP
MVP

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?

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
rahulpawarb
Specialist III
Specialist III

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
Specialist
Specialist
Author

thank you Rahul,

added Year=,Month= and it works

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