How can I delimit the width of a trendline?

[mlarruda]

Hi, I have these variables:

X	Y
1	1
2	5
3	4
4	9
5	7
6	8
7	12
8	20
9	9
10	7
11	4
12	1
13	1
14	2
15	1
16	1

and I want to do a graphic with two visible partitions (X from 1 to 8 and X from 9 to 16). So, I set one dimension X and two expressions IF (X <= 8,Y) and IF (X > 8,Y). It works well and I got this:

Now I want to include the linear trend line. But, after click in the proper option in the expressions tab, I got this:

which is not a good thing to see. I wished something like this:

Anyone knows how can I get a graphic like this last picture?

Many thanks in advance.

[sunny_talwar]

Is this what you want?

May be use Linest_M and Linest_B functions

[Vegar]

Great solution @sunny_talwar. 👍🏻

[sunny_talwar]

Thanks @Vegar 

[mlarruda]

Hi, yes, it is exactly what I want. But I couldn't open your qvw file . When I try there appears a message saying I am a QlikVire Personal Edition user and I can open only files created by myself.

So, could you post the formulas and settings you used to do this chart?

Thank you very much.

[sunny_talwar · Accepted solution]

Dimension

X

4 Expressions

If(X <= 8, Y)
If(X <= 8, (LINEST_M(TOTAL If(X <= 8, Y), X, Y) * X) + LINEST_B(TOTAL If(X <= 8, Y), X, Y))
If(X > 8,Y)
If(X > 8, (LINEST_M(TOTAL If(X > 8, Y), X, Y) * X) + LINEST_B(TOTAL If(X > 8, Y), X, Y))

Color scheme

[mlarruda]

Worked! Thanks a lot!

[mlarruda]

Sorry for bother, but my TRUE database and my TRUE problem are much more complex than this small example and my attempts to adapt this solution are not working. Can I present my TRUE problem here? Can I send it in private to someone available to help me? Or I need to start a new topic?

[sunny_talwar]

Happy to help if you share it here

[mlarruda]

Thank you very much!

Well, ignoring the unrelevant variables, consider I have the following script to launch the data:

LET vDateMin = Num(MakeDate(2015,1,1));
LET vDateMax = Num(MakeDate(2019,12,31));

TempCalendar:

LOAD
Date($(vDateMin) + RowNo() - 1) as DAY0,
AUTOGENERATE 1
WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);

Tab1:

LOAD 
   DAY,
   EVENT,
   WORKER
FROM
[MyFile]

My goal is analyze the totals of distinct events for worker, for month. So, after select the Worker I have interest in, the first chart I did has dimension MONTHNAME(DAY) and expression COUNT (DISTINCT EVENT).

It seemed to work, but there are months in which a given Worker had zero events and the chart skips these months. So, I did a new chart with dimension MONTHNAME(DAY0) and expression
COUNT(DISTINCT IF(MONTHNAME(DAY) = MONTHNAME(DAY0), EVENT)) .

It worked well, but now I need to compare these total of events before vs after a given date. I managed to do it by adding a second dimension, like
IF(DAY0 <= '05/01/2018', Dual('before', 1), Dual('after', 2)).

It worked, but I also need the trend lines and, when I set them on, I got something like the 2nd graphic I posted yesterday, which the lines spanning over all the width of the graphic.

Lastly, I tried to adapt your X and Y solution for my simpler example. I used only MONTHNAME(DAY0) as dimension and the expressions:

IF (DAY <= '05/01/2018', COUNT(DISTINCT IF(MONTHNAME(DAY) = MONTHNAME(DAY0), EVENT)))
IF (DAY > '05/01/2018', COUNT(DISTINCT IF(MONTHNAME(DAY) = MONTHNAME(DAY0), EVENT)))

IF (DAY0 <= '05/01/2018', COUNT(DISTINCT IF(MONTHNAME(DAY) = MONTHNAME(DAY0), EVENT)))
IF (DAY0 > '05/01/2018', COUNT(DISTINCT IF(MONTHNAME(DAY) = MONTHNAME(DAY0), EVENT)))

But none of these pairs of dimensions worked. In both cases, the chart became a blank/empty window.

So, now I am stucked in this point and looking for help.

Thank you very much in advance for any hint you can give me.