topic Re: Trendline (Polynomial order 2) in Qliksense chart in App Development

Trendline (Polynomial order 2) in Qliksense chart

Tue, 15 Aug 2017 17:54:43 GMT

Hi All,

I have chart with dimension "Monthyear" and measure with the expression for running total over the period as below

rangeSum(Above(($(VRMonthlySaving)),0,RowNo()))

I want to add polynomial trend line order 2.

I used below formula below but its not for polynomial. I m using QlikSenes 3 version. Please help. thanks

linest_m(total aggr(if(($(VRMonthlySaving)),($(VRMonthlySaving))), [VRDate.autoCalendar.YearMonth]),[VRDate.autoCalendar.YearMonth])*

Re: Trendline (Polynomial order 2) in Qliksense chart

sunny_talwar — Tue, 15 Aug 2017 17:57:32 GMT

Would you be able to share a sample?

Re: Trendline (Polynomial order 2) in Qliksense chart

sunny_talwar — Tue, 15 Aug 2017 18:01:56 GMT

Or may be check this out

Bug: Quadratic regression

Re: Trendline (Polynomial order 2) in Qliksense chart

Tue, 15 Aug 2017 18:11:33 GMT

Hi Sunny,

I have attached sample data. I want the following chart. blue line I produced with the expression

RangeSum(Above(($(VRMonthlySaving)),0,RowNo())) for running total. If you have any more question please ask.Thanks. Data is pasted here I can not attach in ur reply.

Data

Fiscal Period		F1	F2	F3	F4	F5	F6	F7	F8	Count	VRMonthlySaving
12/2014	01/2015	22								22	$ 4,180.00
01/2015	02/2015	22								22	$ 4,180.00
02/2015	03/2015	7								7	$ 1,330.00
03/2015	04/2015	16								16	$ 3,040.00
04/2015	05/2015	15								15	$ 2,850.00
05/2015	06/2015	18								18	$ 3,420.00
06/2015	07/2015	27								27	$ 5,130.00
07/2015	08/2015	27								27	$ 5,130.00
08/2015	09/2015	14								14	$ 2,660.00
09/2015	10/2015	19								19	$ 3,610.00
10/2015	11/2015	28								28	$ 5,320.00
11/2015	12/2015	34								34	$ 6,460.00
12/2015	01/2016	37								37	$ 7,030.00
01/2016	02/2016	30								30	$ 5,700.00
02/2016	03/2016	37	5							42	$ 7,355.00
03/2016	04/2016	41	3							44	$ 7,985.00
04/2016	05/2016	27	1							28	$ 5,195.00
05/2016	06/2016	33	2							35	$ 6,400.00
06/2016	07/2016	23	2							25	$ 4,500.00
07/2016	08/2016	32	6	12						50	$ 8,678.00
08/2016	09/2016	49	3	9						61	$ 11,161.00
09/2016	10/2016	48	7	13						68	$ 11,967.00
10/2016	11/2016	55	12	24	12					103	$ 17,446.00
11/2016	12/2016	56	10	32	30					128	$ 21,678.00
12/2016	01/2017	60	19	39	48	20				186	$ 33,751.00
01/2017	02/2017	43	5	46	42	23				159	$ 31,010.00
02/2017	03/2017	45	9	41	36	28				159	$ 31,515.00
03/2017	04/2017	71	15	60	51	32	33			262	$ 47,173.00
04/2017	05/2017	62	13	53	49	42	21	32		272	$ 45,939.00
05/2017	06/2017	70	15	63	56	63	20	36		323	$ 57,458.00
06/2017	07/2017	85	4	53	26	48	7	36		259	$ 46,924.00
07/2017	08/2017	55	8	63	46	35	14	24	3	248	$ 42,629.00

Re: Trendline (Polynomial order 2) in Qliksense chart

sunny_talwar — Tue, 15 Aug 2017 18:15:29 GMT

I am not sure how to create the app with this data.... your data have multiple dates? what are F1, F2 here? can you create the chart in a sample and I can try to take it forward from there?

Re: Trendline (Polynomial order 2) in Qliksense chart

Tue, 15 Aug 2017 18:23:37 GMT

can u please use this data ?. this is chart to build. I am able to build chart with blue line (monthly cumulative) I need to add red line as polynomial trend line order 2 . it is done in excel.

Fiscal Period	Cumulative
12/2014	$ 4,180.00
01/2015	$ 5,510.00
02/2015	$ 8,550.00
03/2015	$ 11,400.00
04/2015	$ 14,820.00
05/2015	$ 19,950.00
06/2015	$ 25,080.00
07/2015	$ 27,740.00
08/2015	$ 31,350.00
09/2015	$ 36,670.00
10/2015	$ 43,130.00
11/2015	$ 50,160.00
12/2015	$ 55,860.00
01/2016	$ 63,215.00
02/2016	$ 71,200.00
03/2016	$ 76,395.00
04/2016	$ 82,795.00
05/2016	$ 87,295.00
06/2016	$ 95,973.00
07/2016	$ 107,134.00
08/2016	$ 119,101.00
09/2016	$ 136,547.00
10/2016	$ 158,225.00
11/2016	$ 191,976.00
12/2016	$ 222,986.00
01/2017	$ 254,501.00
02/2017	$ 301,674.00
03/2017	$ 347,613.00
04/2017	$ 405,071.00
05/2017	$ 451,995.00
06/2017	$ 494,624.00
07/2017	$ 494,624.00

Re: Trendline (Polynomial order 2) in Qliksense chart

sunny_talwar — Tue, 15 Aug 2017 18:49:07 GMT

May be like this

Re: Trendline (Polynomial order 2) in Qliksense chart

Tue, 15 Aug 2017 18:51:27 GMT

Yes it seems ok. How did you achieve this in Qlik?

Re: Trendline (Polynomial order 2) in Qliksense chart

Tue, 15 Aug 2017 18:58:31 GMT

Thanks for the solution. But I must say it is very lengthy process would be hard as I have to implement this for various different data. Thanks and still looking for any optimized solution. Hope qlik sense would provide some functionality for tredlines as in excel.

Re: Trendline (Polynomial order 2) in Qliksense chart

sunny_talwar — Tue, 15 Aug 2017 19:39:55 GMT

Unfortunately, it seems that trend lines are not available in Qlik Sense....All the best for your search

Best,

Sunny

Re: Trendline (Polynomial order 2) in Qliksense chart

richbyard — Wed, 04 Oct 2017 14:32:18 GMT

Check the following post for an example:

Calculating trend lines, values and formulas on charts and tables in Qlik Sense

The 2nd Order Polynomial line is not exactly easy to implement but it is possible...