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.

[mlarruda]

Maybe this can help. As a test, I tried the following expressions:

IF (DAY <= '05/01/2018', 10, 20)
IF (DAY > '05/01/2018', 10, 20)

IF (DAY0 <= '05/01/2018', 10, 20)
IF (DAY0 > '05/01/2018', 10, 20)

All of them returned a chart with a line in height 20 and no point in height 10!

I don't know why it happens, but I think that the core of my problem is answer and solve it.

[sunny_talwar]

I don't think I understand the issue well enough to suggest you anything... can you replicate your issue into a sample so that we can see it?

[mlarruda]

Please see the attached files. I want to plot the trend lines (Chart 4) with the blue one spanning only over the blue side and  the red one only over the red side.

Many thanks for any help!

[sunny_talwar]

I believe the very first issue here is that we cannot have 2 dimensions and 2 expressions. If I try to create another expression, the 2nd dimension will be disregarded. So, if we try to create an expression for trend line using Linest_B and Linest_M,  it will falter right away. Is there a reason you have to use 2 dimension?

[mlarruda]

No, this 2nd dimension was the only way I found to split the data into 2 series (before and after the desired date). As I already wrote before, expressions like IF (DATE <= '20/05/2017',...) didn't worked, and I don't know why.

If you can do this split using only one dimension, feel free to go ahead.

[sunny_talwar]

I will check it today and get back to you 🙂

[sunny_talwar]

How does this look?

[mlarruda]

Superb! Can you post the expressions you used?

[sunny_talwar · Accepted solution]

Script

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

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

Tab1:
LOAD WORKER, 
     DAY,
     MonthName(DAY) as MONTHNAME,
     EVENT
FROM [Sample (2).xlsx]
(ooxml, embedded labels, table is Plan1);

Dimension

MONTHNAME0

Expressions

If(MonthStart(Max(DAY0)) <= MakeDate(2017, 5, 20), Count(DISTINCT If(MONTHNAME = MONTHNAME0 and DAY0 <= '20/05/2017', EVENT)))

If(MonthStart(Max(DAY0)) <= MakeDate(2017, 5, 20), 
(LINEST_M(TOTAL Aggr(If(MonthStart(Max(DAY0)) <= MakeDate(2017, 5, 20), Count(DISTINCT If(MONTHNAME = MONTHNAME0 and DAY0 <= '20/05/2017', EVENT))), MONTHNAME0), DAY0) * MonthStart(Max(DAY0)))
+LINEST_B(TOTAL Aggr(If(MonthStart(Max(DAY0)) <= MakeDate(2017, 5, 20), Count(DISTINCT If(MONTHNAME = MONTHNAME0 and DAY0 <= '20/05/2017', EVENT))), MONTHNAME0), DAY0))

If(MonthStart(Max(DAY0)) >= MonthStart(MakeDate(2017, 5, 20)), Count(DISTINCT If(MONTHNAME = MONTHNAME0 and DAY0 > '20/05/2017', EVENT)))

If(MonthStart(Max(DAY0)) >= MonthStart(MakeDate(2017, 5, 20)),
(LINEST_M(TOTAL Aggr(If(MonthStart(Max(DAY0)) >= MonthStart(MakeDate(2017, 5, 20)), Count(DISTINCT If(MONTHNAME = MONTHNAME0 and DAY0 > '20/05/2017', EVENT))), MONTHNAME0), DAY0) * MonthStart(Max(DAY0)))
+LINEST_B(TOTAL Aggr(If(MonthStart(Max(DAY0)) >= MonthStart(MakeDate(2017, 5, 20)), Count(DISTINCT If(MONTHNAME = MONTHNAME0 and DAY0 > '20/05/2017', EVENT))), MONTHNAME0), DAY0))

[mlarruda]

Thank you a lot!