Trendline for a multiple dimension line chart

[#1]

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

[settu_periasamy]

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

[#3]

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..?

[sunny_talwar]

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

[#5]

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

[sunny_talwar]

Something like this?

Script:

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

1

2

];

Chart Dimensions:

RelativeDay&Time

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

Expression

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

[#7]

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

[sunny_talwar]

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?

[#9]

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

[sunny_talwar]

In the script