topic Re: Set analysis - range of dates in QlikView

Set analysis - range of dates

zagzebski — Wed, 04 Jun 2014 14:12:17 GMT

I want to sum an amount within specificed a specfici year/month time frame.

The fields:

Amount

YYYYMM

I want to sum the Amount field for the max of YYYYMM and the preceding 3 months. I know I could do it the long way by adding Max, Max-1, max-2 and max-3 in separate set analysis expressions but thought there would be some way to do it in one concise expression with set analysis.

Thanks in Advance

Steve

Re: Set analysis - range of dates

Wed, 04 Jun 2014 14:18:37 GMT

sum({<YearMonth={'<=$(=max(Date#(YearMonth,'YYYYMM')))>=$(=addmonths(max(Date#(YearMonth,'YYYYMM')),3))'}>}Sales)

you might have play with the date function on top of inner dates depends on how is your field.

Re: Set analysis - range of dates

Gysbert_Wassenaar — Wed, 04 Jun 2014 14:25:17 GMT

Your YYYYMM field can't easlily be calculated with as it is. What you can do is make a date field from it:

Load

...some fields...,

Amount,

date#(YearMonthField, 'YYYYMM') as YearMonthField

From ...;

Once you've done that you can calculate with the YearMonthField using date functions since the underlying values are dates.

sum({<YearMonthField={'>=$(=monthstart(max(YearMonthField)),-3))'}>} Amount)