Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
hhajjali
New 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.

Tags (1)
1 Solution

Accepted Solutions
Partner
Partner

Re: Trendline

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

3 Replies
Partner
Partner

Re: Trendline

Partner
Partner

Re: Trendline

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

hhajjali
New Contributor III

Re: Trendline

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