Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
marcoyukon
Contributor

Excluding ranges from linest_m() + linest_b()

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

1 Solution

Accepted Solutions

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

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)


Capture.PNG

6 Replies

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

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

marcoyukon
Contributor

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

Yes. I've attached it to the post.

marcoyukon
Contributor

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

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

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

Sorry, I forgot... checking now

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

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)


Capture.PNG

marcoyukon
Contributor

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

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

Community Browser