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

diagonjope — Tue, 21 Dec 2021 22:04:53 GMT

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] ;

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

christian77 — Tue, 30 Apr 2019 14:40:17 GMT

Hi.

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

Salutations.

Good luck.

topic Re: Are there any known bugs when using autoCalendar fields in Set Analysis expressions within AGGR in App Development

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

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