topic Re: Average Trend Line in Bar Chart in QlikView

Average Trend Line in Bar Chart

Wed, 25 Nov 2020 16:16:04 GMT

Dear All,

I know there is a Reference Line, but that is not what I am looking for.

What I want is a line with average data points for any year for the whole company group additionally in the bar chart (see graphic).

Is that possible?

See attached qvw.

Re: Average Trend Line in Bar Chart

sunny_talwar — Wed, 20 Jul 2016 14:37:59 GMT

Something like this?

Re: Average Trend Line in Bar Chart

Wed, 20 Jul 2016 14:49:59 GMT

Close, I have removed Company_Peer for the trend line.

But why is it not colored for any company as before and shown in the legend?

Re: Average Trend Line in Bar Chart

sunny_talwar — Wed, 20 Jul 2016 14:55:39 GMT

I guess it has to do with 1 expression vs. 2 expression. When we have 1 expression each dimensions pick up a different color, but with two the colors are distributed across the expression, instead of within the expression. You can use if statement to get this fixed.

Something like this:

=If(Avg(1) > 0, LightMagenta(), LightGray())

UPDATE: So here we have LightGray for Peer companies and LightMagenta for companies.

Re: Average Trend Line in Bar Chart

Wed, 20 Jul 2016 15:00:10 GMT

Is there no other solution or workaround?

I would like to have different colours as defined in the colormap. I have a lot of companies so I think using an if statement is not a good idea.

Second, as I have longer company names than A,B,C I would like to display them in the legend.

Re: Average Trend Line in Bar Chart

sunny_talwar — Wed, 20 Jul 2016 15:02:33 GMT

I guess the only other way I can imagine is to use two charts overlapping each other

Re: Average Trend Line in Bar Chart

Wed, 20 Jul 2016 15:04:04 GMT

Consequence is that I cannot copy the chart as one image to clipboard, right?

Re: Average Trend Line in Bar Chart

sunny_talwar — Wed, 20 Jul 2016 15:06:24 GMT

Yes and some other such as scrolling won't work... etc

Re: Average Trend Line in Bar Chart

Wed, 20 Jul 2016 15:07:47 GMT

That is too bad 😞

Is there a function for the if statement using colour map consecutively?

Re: Average Trend Line in Bar Chart

sunny_talwar — Wed, 20 Jul 2016 15:16:52 GMT

Here is a dynamic way to assign color to companies using ColorMix Wizard. Legend is still a problem

ColorMix1 ((1+Sign(2*(RangeSum(Above(Count({<Company=p(Company)>+<Company=p(Company_Peer)>}Company), 0, RowNo()))-RangeMin (top(total RangeSum(Above(Count({<Company=p(Company)>+<Company=p(Company_Peer)>}Company), 0, RowNo())),1,NoOfRows(total))))/(RangeMax (top(total RangeSum(Above(Count({<Company=p(Company)>+<Company=p(Company_Peer)>}Company), 0, RowNo())),1,NoOfRows(total)))-RangeMin (top(total RangeSum(Above(Count({<Company=p(Company)>+<Company=p(Company_Peer)>}Company), 0, RowNo())),1,NoOfRows(total))))-1)*Sqrt(Fabs((2*(RangeSum(Above(Count({<Company=p(Company)>+<Company=p(Company_Peer)>}Company), 0, RowNo()))-RangeMin (top(total RangeSum(Above(Count({<Company=p(Company)>+<Company=p(Company_Peer)>}Company), 0, RowNo())),1,NoOfRows(total))))/(RangeMax (top(total RangeSum(Above(Count({<Company=p(Company)>+<Company=p(Company_Peer)>}Company), 0, RowNo())),1,NoOfRows(total)))-RangeMin (top(total RangeSum(Above(Count({<Company=p(Company)>+<Company=p(Company_Peer)>}Company), 0, RowNo())),1,NoOfRows(total))))-1))))/2, ARGB(255, 255, 0, 0), ARGB(255, 0, 255, 0))

Re: Average Trend Line in Bar Chart

Tue, 26 Jul 2016 07:13:38 GMT

Many thanks for your help!

On behalf of this, I am having a displaying issue using and island in this context.

Why is company A not displayed in the chart?

Re: Average Trend Line in Bar Chart

jmalenfant — Thu, 04 Aug 2016 18:12:59 GMT

Hi, I am also trying to create an average trend line like you have above and I have a stupid question about opening the sample qvw file. I dragged and dropped it in my Sense Desktop but I can't figure out how to see the graph. I'd like to see what function or operation you used to generate this. Thanks!