Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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.

19 Replies
Michael_Tarallo
Employee
Employee

Hello Ade,

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

Let me know if this helps

Please mark the appropriate replies as CORRECT / HELPFUL so our team and other members know that your question(s) has been answered to your satisfaction.

Regards,

Mike Tarallo

Qlik

Regards,
Mike Tarallo
Qlik
Not applicable
Author

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)

Michael_Tarallo
Employee
Employee

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

Mike

Regards,
Mike Tarallo
Qlik
Not applicable
Author

Thank you !

Michael_Tarallo
Employee
Employee

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

Regards,
Mike Tarallo
Qlik
Not applicable
Author

Mike,

Thanks for the update.


cheers

JonnyPoole
Employee
Employee

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.

Capture.PNG

Not applicable
Author

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

JonnyPoole
Employee
Employee

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))

Not applicable
Author

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

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