Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
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