Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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?
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!
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?
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.
I will check it today and get back to you 🙂
How does this look?
Superb! Can you post the expressions you used?
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))