Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I spent a little time working through the formulas required to mimic the trend lines that are available in QlikView and currently not available in Qlik Sense without knowing how to write reasonably complex expressions.
Here are some examples of exponential and 2nd order polynomial trend lines with the relationship expressed as a formula in the subtitle.
Exponential Trend Line
2nd order polynomial trend line
To make things as clear as possible I have included the Excel variants of these formulas so you have something to reference as I have done whilst checking these formulas calculate correctly. These excel formulas were sourced from Excel Tips From John Walkenbach: Chart Trendline Formulas
The below table shows the equivalent formulas in Qlik for the excel formulas provided. I have also attached a QVF file with examples of each one so that you have something real to reference as you build these into your own applications. In addition I have included the excel file that I was using for testing to ensure my calculations were correct.
Excel Formulas | Qlik Formulas | |
|---|---|---|
Linear Trendline | ||
| Equation | y = m * x + b | y = m * x + b |
| m | = SLOPE(y,x) | = LINEST_M(y,x) |
| b | = INTERCEPT(y,x) | = LINEST_B(y,x) |
Logarithmic Trendline | ||
| Equation | y = (c * LN(x)) + b | y = (c * LOG(x)) + b |
| c | = INDEX(LINEST(y,LN(x)),1) | = LINEST_M(y,LOG(x)) |
| b | = INDEX(LINEST(y,LN(x)),1,2) | = LINEST_B(y,LOG(x)) |
Power Trendline | ||
| Equation | y=c*x^b | y = c * POW( x , b) |
| c | = EXP(INDEX(LINEST(LN(y),LN(x),,),1,2)) | = EXP(LINEST_B(LOG(y),LOG(x))) |
| b | = INDEX(LINEST(LN(y),LN(x),,),1) | = LINEST_M(LOG(y),LOG(x)) |
Exponential Trendline | ||
| Equation | y = c *e ^(b * x) | y = c * POW( e , b * x) |
| c | = EXP(INDEX(LINEST(LN(y),x),1,2)) | = EXP(LINEST_B(LOG(y),x)) |
| b | = INDEX(LINEST(LN(y),x),1) | = LINEST_M(LOG(y),x) |
| e | = EXP(1) | = e() |
| 2nd order Polynomial Trend | ||
| Equation | y = (c2 * x^2) + (c1 * x ^1) + b | y = (c2 * POW(x,2)) + (c1 * x) + b |
| c2 | = INDEX(LINEST(y,x^{1,2}),1) | see example - variable c2 |
| c1 | = INDEX(LINEST(y,x^{1,2}),1,2) | see example - variable c1 |
| b | = INDEX(LINEST(y,x^{1,2}),1,3) | see example - variable b |
Points to note:
- You will see in the QVF that I have used monthly aggregated values from more detailed data which is a real life requirement (you would not necessarily want to create an aggregated table just for this purpose). Therefore the Y values are shown aggregated by the dimension. i.e. aggr(Sum([Expenses (USD)]),MonthYear)
- The 2nd Order Polynomial trend is a little more complex than the others. I have not found a comparable function to excel LINEST(Y,x^{1,2}) so have managed to find some old examples and put together a longhand version. Please see the variables in the example application.
- The 2nd Order Polynomial dimension has some specific requirements in the current form of the expressions. It MUST be a field with distinct values in the dataset, i.e. MonthYear in my example must have the grain of MonthYear and not be a field in the calendar table. It should also join directly to the fact where actuals reside, not join through another dimension.
