LINEST_M AGGR() not performing as expected

[SDT]

Fairly certain this is user error (mine) so hoping for some assistance. I am using the expression below to obtain the trend of revenue over time.

The set analysis is to exclude the current (incomplete) month from the dataset and allow the user to choose a range of other months in the line chart. I added a linear trend line on the chart and in several cases the slope shown for a given product market in the table is the opposite direction of the trend line.

{<[Date.autoCalendar.MonthsAgo]={">0"}>}
LinEst_M(AGGR(SUM(Revenue),[Date.autoCalendar.YearMonth],ProductMarket),[Date.autoCalendar.YearMonth])

The table contains the ProductMarket and the expression above.

[Oleg_Troyansky · Accepted solution]

Hi,

I'd suspect that the derived field [Date.autoCalendar.YearMonth] might be the reason for the problem. Since this derived calendar is generated automatically, we can never be sure how these fields are sorted when the data is being loaded. I recommend trying one of the following:

1. Try adding the sort order to your AGGR dimensions. Something along these lines:

{<[Date.autoCalendar.MonthsAgo]={">0"}>}
LinEst_M(

     AGGR(SUM(Revenue),

          ([Date.autoCalendar.YearMonth], NUMERIC, ASC)

          ,ProductMarket)

,[Date.autoCalendar.YearMonth])

2. If that didn't solve the problem, try generating a proper data field YearMonth and then use that in your AGGR, also sorted in numerical order:

LOAD
...
Date,
Date(MonthStart(Date), 'MMM-YYYY')   as YearMonth,
...
-------------

{<[Date.autoCalendar.MonthsAgo]={">0"}>}
LinEst_M(

     AGGR(SUM(Revenue),

          (YearMonth, NUMERIC, ASC)

          ,ProductMarket)

,YearMonth)

I hope that one of these two solutions solve the problem.

Cheers,

[SDT]

Thank  you for the assist @Oleg_Troyansky !