2 Replies Latest reply: Mar 1, 2018 3:25 PM by Florian Klostermeier RSS

    Calculate linest_b function per row

    Florian Klostermeier
    Florian Klostermeier Mar 1, 2018 3:09 PM

      Hi,

       

      I am new in Qlik Sense. Currently I am working on a dashboard including a trend analysis.

      So far I have learned that the linest function:

      https://help.qlik.com/en-US/sense/September2017/Subsystems/Hub/Content/ChartFunctions/StatisticalAggregationFunctionsinC…

       

      is exactly what I need.

      Unfortunatelly, my data doesn't match the required structure. To use this function properly, you need a X and a Y variable.

      In my case, I don't want to calculate the trend using two variables, but for multiple variables and just one row.

       

      LInest.JPG

       

      In the screenshot above, you can see the structure of my database. Each row is one case (regional area). I have 5 columns/variables indicating the number of events per year (2012-2016). For each geographical area (row) I want to calculate the trend.

       

      Is it possible to change the function or to use another function to calculate the trend across the last 5 years or the predicted value for 2017 without changing the data structure?

       

      If not, how would I need to change the data structure in order to make this function work?

       

      Thanks,

      Florian

      • 67 Views
      • Tags:
        • Re: Calculate linest_b function per row
          Sunny Talwar
          Sunny Talwar Mar 1, 2018 3:15 PM (in response to Florian Klostermeier)

          Is year a dimension or is it created using 5 different measures

            • Re: Calculate linest_b function per row
              Florian Klostermeier
              Florian Klostermeier Mar 1, 2018 3:25 PM (in response to Sunny Talwar )

              Hi Sunny,

               

              thank you very much for your quick reply!
              Actually, each column is a different variable.

               

              Variable 1: XXX_2012

              Variable 2 XXX_2013

              Variable 3: XXX_2014

              Variable 4: XXX_2015

              Variable 5: XXX_2016

               

              (XXX stands for a variable title)

               

              Meaning that the variable name has a decription tag followed by the year. In each row, I have the number of events.

               

              If you look at the screenshot and take "Aalen" as an example. I want to calculate the trend between 2012 and 2016.

              The x values would be 2012 - 2016, the y values would be 5, 5, 5, 7, 7.

               

              Unfortunately, I don't want to calculate the trend using a set of rows but a set of columns, each a different variable.

               

              Thanks and have a great evening - it' evening in Germany and I will go to bed soon.

              Florian