19 Replies Latest reply: Apr 9, 2015 10:15 AM by Ade Jgo

# Adding Avg line in a line chart

What I have :

Annual Income of customers. (CustId, AnnualIncome)

Monthly spending data for customers. (CustId, Month, Amount )

What I want to do :

1. Chart % of income spent by customers  each month ( Amount / AnnualIncome ).

2. Create a 'band' and point out outliers. For example, if you spend more than \$500 above or below the average, you are outside the band.Ideally this band would be bound by two lines (like Reference lines) and have its own background color.

My attempts:

Use line chart and 'Reference lines'.

Problems:

1. The 'Reference lines' are just straight lines, and not the average of a particular month. ( i.e. they should follow the monthly average spend).

2. Don't know how to color the background bound by these lines.

Use combo chart

I can get the 'reference' lines follow the actual average (they are a measure).

Problem:

1. I don't know how to apply multiple dimensions to Combo chart. I tried using drill-down but no luck.

Attaching example with both approaches.

My actual use case is more complicated than this (using aggr, rangesum, above ) and Reference lines don't seem to like that kind of expression.

• ###### Re: Adding Avg line in a line chart

Is that not possible ? seems simple enough thing on other platforms.

• ###### Re: Adding Avg line in a line chart

Which would be the dimensions? Month and Customer? Or just Month?

You can build the Combo chart as you described and visualize for each Month a bar  and one or two points (lines). Also, you could use two Reference lines: one with the 500\$ reference and another one with the total average for the whole year.

• ###### Re: Adding Avg line in a line chart

Both Month and Customer are needed dimensions.  Ideally month would be on x-axis and each line(in chart) represents a customer.

• ###### Re: Adding Avg line in a line chart

Could you post a screenshot of what you try to accomplish (drawing or screenshot of the chart created with another tool)? I'm afraid what you are trying to do is not possible.

• ###### Re: Adding Avg line in a line chart

It should look like this except the Avg and other lines should not be straight likes (they should be real average for respective months).

The expression I am using for Avg Reference line is =Avg(Amount/AnnualIncome).

Thanks

• ###### Re: Adding Avg line in a line chart

I think I get what you are trying to accomplish and I'm afraid it is not possible in the current version.

Just to be sure, I'll ping plu.

• ###### Re: Adding Avg line in a line chart

Hi,

Try this expression

1. Chart % of income spent by customers  each month ( Amount / AnnualIncome ).

=Sum(Amount)/Sum(Total <CustId> Amount)

HOpe this helps you.

Regards,

Jagan.

• ###### Re: Adding Avg line in a line chart

I guess you ment

Sum(Total <CustId> Amount)/SUM(Total <CustId> AnnualIncome)

as the average line. I also tried  Sum(Total <Month> Amount)/SUM(AnnualIncome)

This doesn't work either, it shows a straight line at 10% ( while I am expecting a line that actually represents the monthly average, at around 2%)

Attaching the latest qvf.

• ###### Re: Adding Avg line in a line chart

Folks, no solution to this thing ?

• ###### Re: Adding Avg line in a line chart

Take a look at the solution in this discussion: Re: How to add lineal trend in Qlik sense

Let me know if this helps

Regards,

Mike Tarallo

Qlik

• ###### Re: Adding Avg line in a line chart

Thanks Mike.

The problem I am having is

1. I can't have 2 measures on the line chart. ( I was thinking of making linest_m as another measure).

2. I can't have multiple dimensions on a combo chart.

What i need is Month  and CustomerId as dimensions.

I do not understand the linest_m  very well, this is what interpreted from your example (for my case) but it doesn't work

linest_m(total aggr(if(avg(Amount/AnnualIncome),avg(Amount/AnnualIncome)),Month, CustId),Month, CustId)*

only({1}Month, CustId)+linest_b(total aggr(if(avg(Amount/AnnualIncome),avg(Amount/AnnualIncome)),Month, CustId),Month, CustId)

• ###### Re: Adding Avg line in a line chart

Hi Ade - I see your dilemma - let me check around and see if there are any creative ways that may help.

Mike

• ###### Re: Adding Avg line in a line chart

Thank you !

• ###### Re: Adding Avg line in a line chart

Hi Ade - just a courtesy update - we have a set of eyes on this - there may be some creative ways to do this - so you they will update you here.

Regards,

Mike T

Qlik

• ###### Re: Adding Avg line in a line chart

Mike,

Thanks for the update.

cheers

• ###### Re: Adding Avg line in a line chart

Ade - would any of these options work for you ?  Background banding may be possible in QlikView but not really in Sense, at least for now.

• ###### Re: Adding Avg line in a line chart

Great !

Its pretty creative, I like it. One small thing, can I do something where the line color is automatically picked except the two +500 and -500 lines ? ( I have a lot of accounts and  'if(CustId=1,blue() as LineColor' type expression is not scalable).

I am thinking some thing like NULL for normal accounts (which results in picking up color from palette) and black for those two lines.

Thanks

• ###### Re: Adding Avg line in a line chart

You can do that .

A bit more on the 3 charts. The one on the right is static. Each customer has a measure that is calculated with Set Analysis. There could be a lot of setup which is OK but not if the customer list is not static.

The 2 on the left are dynamic and will support changing customer lists and work for long or short lists.

The one with the legend is autocolored by the engine 'by dimension'.

When you color by expression you can control the line colors . For example the following randomizes the color for customers but keeps black for the +/-500 lines.   The only thing is that the legend disappears which i'm not sure why but would need to investigate.    To enact the following change in the sample, go to either of the line charts, and under 'Colors and Legends' on the right, change the Color from 'auto' to custom and select 'expression' in the drop down. (notice you lose the ability to control/show a legend). then enter the following expression.

if(ChartDimension='500\$ Below Average' or ChartDimension='500\$ Above Average', black(), argb(255,rand()*255,rand()*255,rand()*255))

• ###### Re: Adding Avg line in a line chart

yes, I saw the legend disappearing on custom colors. Will look for that.

This solution is good enough and you have been quite helpful.