aggr in set analysis modifier

[Anonymous]

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!

!

[PrashantSangle]

not getting your requirement.

Try with below set expression.

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

Regards,

[sunny_talwar · Accepted solution]

May be this

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

[Anonymous]

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