Are there any known bugs when using autoCalendar fields in Set Analysis expressions within AGGR ???

[diagonjope]

Greetings!

I am trying to use a Set Analysis expression within the inner aggregation function of an aggr().   When I use a regular field the expression works.  But, if I substitute the regular field for an autoCalendar field, the expression fails.

These are the expressions using the regular field and then the same expressions using the autoCalendar generated field:

Sales Current Month (using regular field):  Sum({$<Month = {$(=max(Month))} >} Amount)

Avg Sales Prev Month (using regular field):   Avg( aggr(sum({$<Month -= {$(=max(Month))}>} Amount), Month, Store))

Both expressions above work fine.  However, only the first of the next two expressions works:

Sales Current Month (using autoCalendar field):

Sum({$<[Date.autoCalendar.NumMonth] = {$(=max([Date.autoCalendar.NumMonth]))} >} Amount)

Avg Sales Prev Months (using autoCalendar field):

Avg( aggr(sum({$<[Date.autoCalendar.NumMonth] -= {$(=max([Date.autoCalendar.NumMonth]))}>} Amount), [Date.autoCalendar.NumMonth], Store))

In my autoCalendar, NumMonth is defined as:  "Num(Month($1)) as NumMonth Tagged ('$month', '$cyclic')".

As can be seen in the img bellow, the expression for Avg Sales Prev Months (shown as a red diamond) doesn't work when using the derived field Date.autoCalendar.NumMonth instead of the Month field:

I am wondering if there are any known bugs / limitations in using derived fields inside a Set Analysis expression in an inner aggregation function of an aggr()

Any ideas? 

--José

PS:  Bellow is the script I used

load *, num(month(Date)) as Month;
Load * inline [
Store, Amount, Date
T1, 120, 1/1/2019
T1, 100, 1/1/2019
T1, 140, 1/1/2019
T1, 130, 2/1/2019
T1, 100, 2/1/2019
T1, 120, 2/1/2019
T1, 80, 3/1/2019
T1, 60, 3/1/2019
T1, 120, 3/1/2019
T1, 150, 4/1/2019
T1, 120, 4/1/2019
T1, 130, 4/1/2019
T1, 80, 5/1/2019
T1, 120, 5/1/2019
T1, 110, 5/1/2019
T1, 110, 6/1/2019
T1, 150, 6/1/2019
T1, 120, 6/1/2019
T1, 160, 7/1/2019
T1, 180, 7/1/2019
T1, 120, 7/1/2019
T1, 200, 8/1/2019
T1, 100, 8/1/2019
T1, 140, 8/1/2019
T1, 60, 9/1/2019
T1, 70, 9/1/2019
T1, 80, 9/1/2019
T1, 90, 10/1/2019
T1, 170, 10/1/2019
T1, 150, 10/1/2019
T1, 20, 11/1/2019
T1, 40, 11/1/2019
T1, 50, 11/1/2019
T1, 300, 12/1/2019
T1, 140, 12/1/2019
T1, 180, 12/1/2019
T2, 30, 1/1/2019
T2, 230, 2/1/2019
T2, 180, 3/1/2019
T2, 120, 4/1/2019
T2, 20, 5/1/2019
T2, 100, 6/1/2019
T2, 190, 7/1/2019
T2, 210, 8/1/2019
T2, 100, 9/1/2019
T2, 160, 10/1/2019
T2, 70, 11/1/2019
T2, 90, 12/1/2019
];

[autoCalendar]:
DECLARE FIELD DEFINITION Tagged ('$date')
FIELDS
Dual(Year($1), YearStart($1)) AS [Year] Tagged ('$axis', '$year'),
Dual('Q'&Num(Ceil(Num(Month($1))/3)),Num(Ceil(NUM(Month($1))/3),00)) AS [Quarter] Tagged ('$quarter', '$cyclic'),
Dual(Year($1)&'-Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [YearQuarter] Tagged ('$yearquarter', '$qualified'),
Dual('Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [_YearQuarter] Tagged ('$yearquarter', '$hidden', '$simplified'),
Month($1) AS [Month] Tagged ('$month', '$cyclic'),
Num(Month($1)) as NumMonth Tagged ('$month', '$cyclic'),
Day($1) as Day Tagged ('$day', '$cyclic'),
Dual(Year($1)&'-'&Month($1), monthstart($1)) AS [YearMonth] Tagged ('$axis', '$yearmonth', '$qualified'),
Dual(Month($1), monthstart($1)) AS [_YearMonth] Tagged ('$axis', '$yearmonth', '$simplified', '$hidden'),
Dual('W'&Num(Week($1),00), Num(Week($1),00)) AS [Week] Tagged ('$weeknumber', '$cyclic'),
Date(Floor($1)) AS [Date] Tagged ('$axis', '$date', '$qualified'),
Date(Floor($1), 'D') AS [_Date] Tagged ('$axis', '$date', '$hidden', '$simplified'),
If (DayNumberOfYear($1) <= DayNumberOfYear(Today()), 1, 0) AS [InYTD] ,
Year(Today())-Year($1) AS [YearsAgo] ,
If (DayNumberOfQuarter($1) <= DayNumberOfQuarter(Today()),1,0) AS [InQTD] ,
4*Year(Today())+Ceil(Month(Today())/3)-4*Year($1)-Ceil(Month($1)/3) AS [QuartersAgo] ,
Ceil(Month(Today())/3)-Ceil(Month($1)/3) AS [QuarterRelNo] ,
If(Day($1)<=Day(Today()),1,0) AS [InMTD] ,
12*Year(Today())+Month(Today())-12*Year($1)-Month($1) AS [MonthsAgo] ,
Month(Today())-Month($1) AS [MonthRelNo] ,
If(WeekDay($1)<=WeekDay(Today()),1,0) AS [InWTD] ,
(WeekStart(Today())-WeekStart($1))/7 AS [WeeksAgo] ,
Week(Today())-Week($1) AS [WeekRelNo] ;

DERIVE FIELDS FROM FIELDS [Date] USING [autoCalendar] ;

[christian77]

Hi.

I've read your data set, and I don't think you should have any problem using aggr functions.

Salutations.

Good luck.