topic Re: Excluding ranges from linest_m() + linest_b() in App Development

Excluding ranges from linest_m() + linest_b()

marcoyukon — Mon, 13 Mar 2017 16:04:46 GMT

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

Re: Excluding ranges from linest_m() + linest_b()

sunny_talwar — Mon, 13 Mar 2017 17:16:16 GMT

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

Re: Excluding ranges from linest_m() + linest_b()

marcoyukon — Tue, 14 Mar 2017 13:28:52 GMT

Yes. I've attached it to the post.

Re: Excluding ranges from linest_m() + linest_b()

marcoyukon — Wed, 15 Mar 2017 16:07:54 GMT

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

Re: Excluding ranges from linest_m() + linest_b()

sunny_talwar — Wed, 15 Mar 2017 16:11:17 GMT

Sorry, I forgot... checking now

Re: Excluding ranges from linest_m() + linest_b()

sunny_talwar — Wed, 15 Mar 2017 16:19:00 GMT

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)

Re: Excluding ranges from linest_m() + linest_b()

marcoyukon — Wed, 15 Mar 2017 16:26:21 GMT

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