Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2023, a live, in-person thrill ride. April 17 - 20, 2023, in Las Vegas! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
hhajjali
Contributor III
Contributor III

Trendline

Hello,

I have the following table:

   

CommandNB ITEMSCOST
Commad 1650
Commad 2430
Commad 3535
Commad 41060
Commad 5535
Commad 61465
Commad 7855
Commad 81045

And I created a graph of average item cost per command, but I need to add a trend line. I tried multiple ideas posted on the community but I did not succeed to have my trend line.

Example:

  1. linest_m(total aggr(if(COST/[NB ITEMS],COST/[NB ITEMS]),Command),Command)* 
  2. only({1}Command)+linest_b(total aggr(if(COST/[NB ITEMS],COST/[NB ITEMS]),Command),Command)

bellow graph in excel, and the trendline in orange I need in qlick sense:

Sans titre.png

Thanks a lot for your help.

1 Solution

Accepted Solutions
OmarBenSalem

First ,

Create a new dimension is ur script (numeric) as follow:

subfield(Command,' ',2) as CommandNum, (this is to transform ur commands from command 1 to 1 etc

then, use a combined graph;

as dimension:

='Command '&CommandNum

as a measure:

1)sum(COST) / Sum([NB ITEMS]) : as bar

2)

linest_m(total aggr(sum(COST) / Sum([NB ITEMS]),CommandNum),CommandNum)*CommandNum

+linest_b(total aggr(sum(COST) / Sum([NB ITEMS]),CommandNum),CommandNum)  : as line

Capture.PNG

sort as follow:

Capture.PNG

result:

Capture.PNG

View solution in original post

4 Replies
OmarBenSalem

First ,

Create a new dimension is ur script (numeric) as follow:

subfield(Command,' ',2) as CommandNum, (this is to transform ur commands from command 1 to 1 etc

then, use a combined graph;

as dimension:

='Command '&CommandNum

as a measure:

1)sum(COST) / Sum([NB ITEMS]) : as bar

2)

linest_m(total aggr(sum(COST) / Sum([NB ITEMS]),CommandNum),CommandNum)*CommandNum

+linest_b(total aggr(sum(COST) / Sum([NB ITEMS]),CommandNum),CommandNum)  : as line

Capture.PNG

sort as follow:

Capture.PNG

result:

Capture.PNG

hhajjali
Contributor III
Contributor III
Author

Many Thanks! The step I missed is X axis should be numeric and not text.

Rakoto
Contributor
Contributor

Could you please help why the trend curve is not straight?