topic aggr in set analysis modifier in QlikView

aggr in set analysis modifier

Anonymous — Wed, 26 Jul 2017 12:15:28 GMT

Hi there,

I'm sure this is a quick one but I cant seem to figure it out.....

I am loading in the below test script

dates:
load * Inline [
date, pubd, sales
01/01/2017, 01/01/2016,1
01/01/2017, 01/02/2015,1
01/01/2017, 01/02/2014,1
01/01/2018, 01/01/2017,1
01/01/2018, 01/02/2016,1
01/01/2018, 01/02/2015,1
];

dates_final:
NoConcatenate Load
date(date) as date,
num(date(date)) as datenum,
num(date(date)) as pubend,
num(date(date))-730 as pubstart,
date(pubd) as pubd,
num(date(pubd)) as pubdnum,
sales
Resident dates;
drop table dates;

I am then using

=Sum({<pubdnum={">=$(=addmonths(Min(datenum),-24)) <=$(=Max(datenum))"}>} sales)

but would like ={">=$(=addmonths(Min(datenum),-24)) <=$(=Max(datenum))"}>} set modifier to be aggregated over datenum dimension, so I return 4 records and not 6.

={">=$(=addmonths(Min(datenum),-24)) <=$(=Max(datenum))"}>} is currently across the whole set and so returning 6 records.

Please see test application and screen-print attached.

Thanks in advance!

Re: aggr in set analysis modifier

PrashantSangle — Wed, 26 Jul 2017 12:27:25 GMT

not getting your requirement.

Try with below set expression.

=Sum({<pubdnum={">=$(=num(addmonths(Min(datenum),-24))) <=$(=Max(datenum))"}>} sales)

Regards,

Re: aggr in set analysis modifier

sunny_talwar — Wed, 26 Jul 2017 12:29:51 GMT

May be this

=Sum(Aggr(If(pubdnum >= addmonths(Min(TOTAL <date> datenum),-24) and pubdnum <= Max(TOTAL <date> datenum), sales), pubdnum, date))

Re: aggr in set analysis modifier

Anonymous — Wed, 26 Jul 2017 12:31:06 GMT

AWESOME, this worked Sunny, thanks!!!!!