Excluding ranges from linest_m() + linest_b()

[marcoyukon]

Liner Trend expression:

linest_m(total aggr(if(sum(PaidAmount),sum(PaidAmount)),DateOfService),DateOfService)*MonthOfService

+

linest_b(total aggr(if(sum(PaidAmount),sum(PaidAmount)),DateOfService),DateOfService)

I need to exclude the last three months from the linear trend only. How can this be done ? I've tried set analysis after the aggr function, but may be doing something wrong.

See attached example: 2017-01,2017-02, 2017-03.

Regards

[sunny_talwar]

Would you be able to share a sample to test this out?

[marcoyukon]

Yes. I've attached it to the post.

[marcoyukon]

Hi Sunny, were you able to test it out with the attachment I sent ? Thank you.

[sunny_talwar]

Sorry, I forgot... checking now

[sunny_talwar · Accepted solution]

May be this

linest_m({<MonthOfService = {"$(='<=' & Date(AddMonths(Max(MonthOfService), -3), 'YYYY-MM'))"}>}total aggr(if(sum(PaidAmount),sum(PaidAmount)),DateOfService),DateOfService)*MonthOfService //only({1}MonthOfService)

+

linest_b({<MonthOfService = {"$(='<=' & Date(AddMonths(Max(MonthOfService), -3), 'YYYY-MM'))"}>}total aggr(if(sum(PaidAmount),sum(PaidAmount)),DateOfService),DateOfService)

[marcoyukon]

I was trying to apply the set to the aggregation rather than using it on the linest functions. It makes sense now.  Thank you!