topic Re: LINEST_M AGGR() not performing as expected in App Development

LINEST_M AGGR() not performing as expected

SDT — Thu, 06 Mar 2025 16:05:27 GMT

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.

Re: LINEST_M AGGR() not performing as expected

Oleg_Troyansky — Thu, 06 Mar 2025 17:39:50 GMT

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,

Re: LINEST_M AGGR() not performing as expected

SDT — Mon, 10 Mar 2025 17:19:41 GMT

Thank you for the assist @Oleg_Troyansky !