topic Re: Trendline for a multiple dimension line chart in QlikView

Trendline for a multiple dimension line chart

Wed, 25 Nov 2020 16:16:04 GMT

Is there an easy way to create an 'average' trendline for a line chart with 2 dimensions? I have the following, and wish to produce an average of the 4 lines on the same chart, but since I have 2 dimensions, clicking the Average trendline box produces 4 additional trendlines, instead of 1. I'd rather not have to create a transparent chart on top if possible. Thanks

Re: Trendline for a multiple dimension line chart

settu_periasamy — Fri, 29 Apr 2016 18:47:44 GMT

Hi,

May be try this..

Instead of Checking Average Trend line for each expression, Create one more Expression , Uncheck the line and Check the Average in the Trend lines Part (this expression only)

and check the value on Data points

Re: Trendline for a multiple dimension line chart

Fri, 29 Apr 2016 20:37:36 GMT

I don't think the trendline is what I'm looking for actually. I essentially need to be able to produce an average at each x axis interval, and connect the dots. Perhaps this is not do-able..?

Re: Trendline for a multiple dimension line chart

sunny_talwar — Fri, 29 Apr 2016 21:49:00 GMT

May be share a sample with your expectation for us to play around with

Re: Trendline for a multiple dimension line chart

Thu, 05 May 2016 19:20:27 GMT

I'm trying to get the sales lines for Companies A and B to populate, as well as the average line for both selections. Thanks for the help

Re: Trendline for a multiple dimension line chart

sunny_talwar — Fri, 06 May 2016 01:28:52 GMT

Something like this?

Script:

LOAD * Inline

[DateA, ID, Company, Sales, RelativeDay&Time

05012016, 1, A, 0, -1

05012016, 2, B, 30, -1

05022016, 1, A, -10, 1

05022016, 2, B, 30, 1

05032016, 1, A, -20, 2

05032016, 2, B, 50, 2

];

Dim:

LOAD * Inline [

Dim

];

Chart Dimensions:

RelativeDay&Time

=Pick(Dim, Dual('Company= ' & Company & Chr(13) & 'Date: ' & DateA, ID), 'Average')

Expression

Pick(Dim, Sum(Sales), Avg(Sales))

Re: Trendline for a multiple dimension line chart

Mon, 09 May 2016 16:38:19 GMT

Can you explain how the Pick function knows which expression (Sum or Avg) to 'Pick'? I'm trying to replicate for a much larger data set, but am unclear how exactly it's picking an expression to display, as well as how both the sum and average are displayed at the same time

Re: Trendline for a multiple dimension line chart

sunny_talwar — Mon, 09 May 2016 16:45:39 GMT

Pick function works similar to the if statement. Pick would look at the number associated with value of Dim (which in this case is 1 and 2). So When Dim = 1, use Sum(Sales), when it is 2 use Avg(Sales). This works, because I have use Pick(Dim in the dimension also.

=Pick(Dim, Dual('Company= ' & Company & Chr(13) & 'Date: ' & DateA, ID), 'Average')

Here when Dim = 1, use Dual('Company= ' & Company & Chr(13) & 'Date: ' & DateA, ID) as the dimension and when Dim = 2, then use 'Average' (static value) as dimension.

So combining the two, we get

For Dual('Company= ' & Company & Chr(13) & 'Date: ' & DateA, ID) -> use Sum(Sales)

For 'Average' -> Use Avg(Sales)

Does this make sense?

Re: Trendline for a multiple dimension line chart

Mon, 09 May 2016 18:07:10 GMT

I understand that, but where/when does DIm get set to 1 or 2?

Re: Trendline for a multiple dimension line chart

sunny_talwar — Mon, 09 May 2016 18:16:15 GMT

In the script

Re: Trendline for a multiple dimension line chart

Mon, 09 May 2016 18:25:45 GMT

Why are both the Sum and Avg displayed in the below instance, but when selecting only one company, just the sum(sales) is displayed? (And sorry, I was just playing around and added Company C to the original file). Just trying to understand the relationship between the 'Dim Load Inline 1, 2' you touched on, and the first field in the Pick function (the 'Dim') in the chart

Re: Trendline for a multiple dimension line chart

sunny_talwar — Mon, 09 May 2016 18:40:45 GMT

When you select just one company the two average lines overlap because average of 10 = 10

Re: Trendline for a multiple dimension line chart

Mon, 09 May 2016 18:44:03 GMT

So neither Sum(Sales) or Avg(Sales) is ever 'Picked'? I'm not really sure what function Pick is serving if both expressions are always displayed

Re: Trendline for a multiple dimension line chart

sunny_talwar — Mon, 09 May 2016 19:23:38 GMT

Pick is deciding which specific dimension value should get assigned which expression. So looking at it from the straight table view might make things easier to understand

When my dimension is Average, use the Avg(Sales) Expression. and when the dimension is Company = A or Company = B, then use Sum(Sales)

This dimension in itself is created using the pick function.

Re: Trendline for a multiple dimension line chart

stevedark — Mon, 09 May 2016 19:32:22 GMT

Hi Sean,

Take a look at this blog post, it may provide a solution for you:

http://www.quickintelligence.co.uk/qlikview-accumulate-values/

If you look at the part about the average bar in a bar chart, I think that is what you need. You simply have to create a new data island that links each of the dimensions to itself and to the 'average' dimension.

You may have to extrapolate the solution out a bit further, so that you have a single dimension field with five values and then the expression behaves differently based on which value in the dimension is in play.

Hope that gives you some ideas on how to approach this problem?

Steve

Re: Trendline for a multiple dimension line chart

Mon, 23 May 2016 19:26:23 GMT

Hey guys - I'm having issues on a similar chart with similar data, but the expression is a % return. Can you take a look at the attached? I need to have a dynamic average of the two lines (Company A & B) show up, but when I add an additional expression (currently not enabled), it only charts the Average line, when I need it to plot all 3 of the lines together. Thanks again for the help!