Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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.