Add the average line to a line chart with multiple lines

[#1]

Hi - I'm trying to add an average line to my chart (attached).  I would like the average to be based on each of the score categories (000-100, 101-200, etc.).  For example, the average displayed for 000-100 should be the average of 19.2% and 18.4%, and the average displayed for 901-1000 should be the average of 15.9% and 15.2%.  I've spent quite a while on the forum and haven't been able to find a solution to this.  Any insight is greatly appreciated!

[#2]

Good idea to use another chart. Hopefully someone comes through with another solution.

Best,

Matt

[MarcoWedel · Accepted solution]

Hi,

maybe this helps?

based on your sample data:

LOAD pet_proj_nm, 
     unique_id, 
     prospect_score_cat, 
     resp_open_ind, 
     cnt_tran
FROM [http://community.qlik.com/thread/129247]
(html, codepage is 1252, embedded labels, table is @1);

regards

Marco

[#4]

Oh my gosh, this is EXACTLY what I needed Marco!!  Thank you so very much!

Thanks to everyone else who has given me suggestions

[MarcoWedel]

you're welcome.

regards

Marco

[#6]

Hi Marco,

Another question - The way the formula is currently written also averages on zero values (please see red circle below).  In this instance, the average value for 000-100 should be the same as "Frozen-Atlanta", i.e. 0.05%, because Frozen - Brooklyn has no one in that 000-100 category.  Is there a way to modify the formula to average on only the relevant dimensions?

[Anonymous]

Hi Macro,

I understand your example.

But for my case, it is slightly different, There is only two options for Pet_proj_nm, I don't know the number of options since it depends on the user filter. So how can I modify code for my case?

Thanks and best regards,

Jerry