Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
anandathome
Creator
Creator

Trendline using linest_ function within a range

Hi Experts,

     I have the following data:

MonthYearSalesQty
Jan-1310000
Feb-1311000
Mar-1315000
Apr-138500
May-130
Jun-130
Jul-130
Aug-130
Sep-130
Oct-130
Nov-130
Dec-130

I am using the following expression to get trendline:

LINEST_M(TOTAL aggr(sum(SalesQty),MonthYear),MonthYear)*MonthYear + linest_b(total aggr(sum(SalesQty),MonthYear),MonthYear)

This takes even the zero values of salesqty and plot the trend (which give not the very best trend).

Is is possible to specify in linest_m and linest_b function to take only the range from Jan-2013 to April-2013 into account for plotting the trendline?

OR

If there is any other way, please answer.

1 Solution

Accepted Solutions
anandathome
Creator
Creator
Author

I found something helpful on the post:

http://community.qlik.com/thread/53721

and this solves my problem.

Thanks to dtalbot777

View solution in original post

2 Replies
anandathome
Creator
Creator
Author

Hi Everyone,

     I could not find any solution on any other discussion.

Could anyone please help me on this?

I basically want linest_m function to use only a limited time period to project the trendline.

Linest.bmp

The red-dotted line gives the forecast using linest_m and linest_b function

Expression as:

=LINEST_M(TOTAL aggr(sum(OrderQty),YearMonth),YearMonth)* YearMonth

+ linest_b(total aggr(sum(OrderQty),YearMonth),YearMonth)

Is there any way I can ask the linest_m and linest_b function to use the values from 201204 to 201303 to use as history and project the trendline?

anandathome
Creator
Creator
Author

I found something helpful on the post:

http://community.qlik.com/thread/53721

and this solves my problem.

Thanks to dtalbot777