Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
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] ;
Hi.
I've read your data set, and I don't think you should have any problem using aggr functions.
Salutations.
Good luck.