Newbie question with trendlines

[Remco]

Hi all,

I have a dataset with records for car sales. Each sale belong to a period being like 201701, 201702, 201712, 201801, etc. For each sales I have a performance calculated and that is plotted in a graphic. Now, in PowerBi we can make automatic trendlines as long as I change the dimension to a date field which is als fine when it gets grouped into year/months.

Now in Qlik I can make these lines my self but cannot figure it out. First of all I am not too familiar with the formulas behind the trendlines but I am able to reproduce in Excel.

Now, I know the formula is y = (c * LOG(x)) + b

I am able to calculate both c and b as:

b = LINEST_M(y,LOG(x)) and

c = LINEST_B(y,LOG(x))

I found out that using the dimension "PERIOD" as earlier mentioned is not suitable for calculating, first problem. For the time being I replaced my PERIODS with just 1,2,3,4 etc. being the name of this field "period_"

b I calculate as = LINEST_M(PIVOTSALESPERFORMANCE,LOG(Periode_)) and

c I calculate as = LINEST_B(PIVOTSALESPERFORMANCE,LOG(Periode_)) which for the moment I see as:

and, the expected logarithmic performance I calculate like: = (LINEST_B(PIVOTSALESPERFORMANCE,LOG(Periode_)) * LOG(Periode_)) + LINEST_M(PIVOTSALESPERFORMANCE,LOG(Periode_))

But what I get is this:

Questions:

How do I get the correct trend performance to each pont?

How can I use the "PERIOD" instead of the temporary "Periode_"?

Thanks!

[Remco]

The problem is clearly that I don´t understand how to get the b and c values in the respective table. Then ofcourse the issue with wanting to use PERIOD instead of the 1, 2 3 etc.