topic Re: Calculate linest_b function per row in App Development

Calculate linest_b function per row

florian_kloster — Thu, 01 Mar 2018 20:09:45 GMT

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.

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

Re: Calculate linest_b function per row

sunny_talwar — Thu, 01 Mar 2018 20:15:02 GMT

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

Re: Calculate linest_b function per row

florian_kloster — Thu, 01 Mar 2018 20:25:12 GMT

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