Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Save $600 on Qlik Connect registration! Sign up by Dec. 6 to get an extra $100 off with code CYBERSAVE: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
mlarruda
Creator II
Creator II

How can I delimit the width of a trendline?

Hi, I have these variables:

XY
11
25
34
49
57
68
712
820
99
107
114
121
131
142
151
161

 

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:

Captura1.PNG

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

Captura2.PNG

 

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

Captura3.PNG

 

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

Many thanks in advance.

Labels (1)
2 Solutions

Accepted Solutions
sunny_talwar

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

image.png

View solution in original post

sunny_talwar

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))

View solution in original post

19 Replies
sunny_talwar

Is this what you want?

Capture.PNG

May be use Linest_M and Linest_B functions

Vegar
MVP
MVP

Great solution @sunny_talwar. 👍🏻
sunny_talwar

Thanks @Vegar 

mlarruda
Creator II
Creator II
Author

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

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

image.png

mlarruda
Creator II
Creator II
Author

Worked! Thanks a lot!

mlarruda
Creator II
Creator II
Author

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
Creator II
Creator II
Author

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.