topic Re: Set Analysis - Addmonths in QlikView

Set Analysis - Addmonths

ilanbaruch — Tue, 14 Feb 2017 11:13:00 GMT

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)

Re: Set Analysis - Addmonths

Chanty4u — Tue, 14 Feb 2017 11:17:04 GMT

hi,

try below

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

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

Re: Set Analysis - Addmonths

Anil_Babu_Samineni — Tue, 14 Feb 2017 11:51:47 GMT

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?

Re: Set Analysis - Addmonths

rahulpawarb — Tue, 14 Feb 2017 12:09:24 GMT

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

Re: Set Analysis - Addmonths

ilanbaruch — Tue, 14 Feb 2017 19:32:47 GMT

thank you Rahul,

added Year=,Month= and it works

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