Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
ilanbaruch
Contributor III

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
Valued Contributor III

Re: Set Analysis - Addmonths

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
Esteemed Contributor III

Re: Set Analysis - Addmonths

hi,

try below

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


or


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

Re: Set Analysis - Addmonths

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?

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
Valued Contributor III

Re: Set Analysis - Addmonths

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

ilanbaruch
Contributor III

Re: Set Analysis - Addmonths

thank you Rahul,

added Year=,Month= and it works

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