10 Replies Latest reply: Oct 4, 2017 10:33 AM by Richard Byard RSS

    Trendline (Polynomial order 2) in Qliksense chart

    Saima SM

      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

          Would you be able to share a sample?

            • Re: Trendline (Polynomial order 2) in Qliksense chart
              Saima SM

              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 PeriodF1F2F3F4F5F6F7F8CountVRMonthlySaving
              12/201401/201522 22 $          4,180.00
              01/201502/201522 22 $          4,180.00
              02/201503/20157 7 $          1,330.00
              03/201504/201516 16 $          3,040.00
              04/201505/201515 15 $          2,850.00
              05/201506/201518 18 $          3,420.00
              06/201507/201527 27 $          5,130.00
              07/201508/201527 27 $          5,130.00
              08/201509/201514 14 $          2,660.00
              09/201510/201519 19 $          3,610.00
              10/201511/201528 28 $          5,320.00
              11/201512/201534 34 $          6,460.00
              12/201501/201637 37 $          7,030.00
              01/201602/201630 30 $          5,700.00
              02/201603/2016375 42 $          7,355.00
              03/201604/2016413 44 $          7,985.00
              04/201605/2016271 28 $          5,195.00
              05/201606/2016332 35 $          6,400.00
              06/201607/2016232 25 $          4,500.00
              07/201608/201632612 50 $          8,678.00
              08/201609/20164939 61 $       11,161.00
              09/201610/201648713 68 $       11,967.00
              10/201611/201655122412 103 $       17,446.00
              11/201612/201656103230 128 $       21,678.00
              12/201601/20176019394820 186 $       33,751.00
              01/201702/2017435464223 159 $       31,010.00
              02/201703/2017459413628 159 $       31,515.00
              03/201704/2017711560513233 262 $       47,173.00
              04/201705/201762135349422132 272 $       45,939.00
              05/201706/201770156356632036 323 $       57,458.00
              06/201707/2017854532648736 259 $       46,924.00
              07/201708/201755863463514243248 $       42,629.00

               

                • Re: Trendline (Polynomial order 2) in Qliksense chart
                  Sunny Talwar

                  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
                      Saima SM

                      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 PeriodCumulative
                      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
                  Richard Byard

                  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...

                  pic2 - polynomial.PNG