topic Re: Add the average line to a line chart with multiple lines in QlikView

Add the average line to a line chart with multiple lines

Tue, 12 Aug 2014 23:04:57 GMT

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!

Re: Add the average line to a line chart with multiple lines

Wed, 13 Aug 2014 03:12:15 GMT

Perhaps you could try:

avg(aggr(sum(Field1),Dim1))

Where Field1 is Prospect_Cat_Score and Dim1 is the field you want to aggregate over, Frozen Atlanta and Frozen New York.

Word of caution though, you cannot average averages, the result will be incorrect. You should be using the absolute values to recalculate the average.

Hope that helps.

Matt

Re: Add the average line to a line chart with multiple lines

Wed, 13 Aug 2014 19:49:20 GMT

Hi Carey, were you able to solve this?

Best,

Matt

Re: Add the average line to a line chart with multiple lines

MarcoARaymundo — Wed, 13 Aug 2014 20:18:01 GMT

First: if you use the "UNIT (Frozen Atlanta)" like dimension, remove this.

Add expressions like this: Sum({<UNIT={'Frozen Atlanta'}>} VALUE) and named Frozen Atlanta. Repeat this to Fozen Brooklin.

For average add expression: AVG({<UNIT={'*'}>} VALUE) and named average.

Re: Add the average line to a line chart with multiple lines

Wed, 13 Aug 2014 20:34:02 GMT

Thank you guys for your suggestions. The issue is, I don't want to hard code the expressions, since I'd like the user to be able to select the project names and then display the results accordingly. I've attached a screenshot of the two dimensions and the expression I have so far. The "pet_proj_nm" field corresponds to Frozen Atlanta etc, which should be dynamically selected.

Re: Add the average line to a line chart with multiple lines

MarcoWedel — Wed, 13 Aug 2014 21:00:14 GMT

can you please post some sample data

thanks

regards

Marco

Re: Add the average line to a line chart with multiple lines

Wed, 13 Aug 2014 23:33:44 GMT

Hi Carey,

If you attach some data I'm sure we'll solve this faster. In the meantime, let me make sure I understand what you need. You've got this expression:

sum(resp_open_ind)/sum(count_cst)

You want to calculate the overall average for each score category, right? To do this, you could try:

sum(total <prospect_score_cat > resp_open_ind)/sum(total<prospect_score_cat > count_cst)

The total <> will aggregate over prospect_score_cat while ignoring the categories. I think this will allow you to calculate the average.

Best,

Matt

Re: Add the average line to a line chart with multiple lines

Thu, 14 Aug 2014 21:22:10 GMT

Thank you all. Here are some sample data for two campaigns.

pet_proj_nm	unique_id	prospect_score_cat	resp_open_ind	cnt_tran
Frozen - Atlanta	U_103089151	801-900	0	1
Frozen - Atlanta	U_181230272	901-1000	0	1
Frozen - Atlanta	U_93147111	901-1000	1	1
Frozen - Atlanta	U_179205676	701-800	0	1
Frozen - Atlanta	U_83974758	801-900	0	1
Frozen - Atlanta	U_65797158	901-1000	1	1
Frozen - Atlanta	U_68806048	801-900	1	1
Frozen - Atlanta	U_77061818	801-900	0	1
Frozen - Atlanta	U_175996931	901-1000	1	1
Frozen - Atlanta	U_84699665	801-900	0	1
Frozen - Atlanta	U_190622182	901-1000	0	1
Frozen - Atlanta	U_63637894	801-900	0	1
Frozen - Atlanta	U_171898435	801-900	0	1
Frozen - Atlanta	U_177005746	801-900	0	1
Frozen - Atlanta	U_182376476	901-1000	1	1
Frozen - Atlanta	U_187151165	901-1000	1	1
Frozen - Atlanta	U_197030067	101-200	0	1
Frozen - Atlanta	U_62110649	701-800	0	1
Frozen - Atlanta	U_193029294	801-900	0	1
Frozen - Atlanta	U_192368656	701-800	1	1
Frozen - Atlanta	U_184599878	701-800	0	1
Frozen - Atlanta	U_62796611	901-1000	0	1
Frozen - Atlanta	U_198194170	501-600	0	1
Frozen - Atlanta	U_65645476	801-900	0	1
Frozen - Atlanta	U_194627020	801-900	0	1
Frozen - Atlanta	U_87018069	801-900	0	1
Frozen - Atlanta	U_73016019	701-800	1	1
Frozen - Atlanta	U_197628321	801-900	0	1
Frozen - Brooklyn	U_78854528	701-800	0	1
Frozen - Brooklyn	U_193333226	901-1000	0	1
Frozen - Brooklyn	U_70315911	901-1000	0	1
Frozen - Brooklyn	U_47777695	701-800	0	1
Frozen - Brooklyn	U_195260574	901-1000	0	1
Frozen - Brooklyn	U_191148379	901-1000	0	1
Frozen - Brooklyn	U_88431645	801-900	0	1
Frozen - Brooklyn	U_189185640	801-900	0	1
Frozen - Brooklyn	U_188648180	801-900	0	1
Frozen - Brooklyn	U_66749088	901-1000	0	1
Frozen - Brooklyn	U_183496552	901-1000	0	1
Frozen - Brooklyn	U_179399424	801-900	1	1
Frozen - Brooklyn	U_192366521	701-800	0	1
Frozen - Brooklyn	U_66962191	801-900	0	1
Frozen - Brooklyn	U_182515681	801-900	0	1
Frozen - Brooklyn	U_197041469	901-1000	0	1
Frozen - Brooklyn	U_192882708	901-1000	0	1
Frozen - Brooklyn	U_80302045	801-900	0	1
Frozen - Brooklyn	U_184120826	701-800	0	1
Frozen - Brooklyn	U_174324480	301-400	0	1
Frozen - Brooklyn	U_198382941	701-800	0	1
Frozen - Brooklyn	U_190374501	901-1000	0	1

Matt - Your suggestion did give me the average (red line in the picture); however, after adding that expression, I no longer see a separate line for each of the two campaigns. Instead, the blue line basically connects the value for the two campaigns within each score category.

Re: Re: Add the average line to a line chart with multiple lines

Thu, 14 Aug 2014 23:32:40 GMT

Hi Carey,

I can only get it to work if I specify Frozen Atlanta or Brooklyn in the expression, see attached. Looks like there may be a limitation to doing this if you use more than 1 dimension in the chart.

Hope that will work for you or perhaps someone else knows another way.

Matt

Re: Re: Add the average line to a line chart with multiple lines

Thu, 14 Aug 2014 23:50:14 GMT

Yeah, thanks Matt. The second dimension seems to be the issue here, and unfortunately I can't remove that because we want to be dynamic based on the user's selection.

The alternative that I am using (for now) is to create a second chart that plots just the overall average, and overlay it on top - Definitely not desirable, but there does not appear to be another way. Greatly appreciate it if anybody else has other suggestions!

Re: Re: Add the average line to a line chart with multiple lines

Fri, 15 Aug 2014 00:58:35 GMT

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

Best,

Matt

Re: Add the average line to a line chart with multiple lines

MarcoWedel — Fri, 15 Aug 2014 15:03:57 GMT

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

Re: Add the average line to a line chart with multiple lines

Fri, 15 Aug 2014 17:04:31 GMT

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

Thanks to everyone else who has given me suggestions

Re: Add the average line to a line chart with multiple lines

MarcoWedel — Fri, 15 Aug 2014 22:04:52 GMT

you're welcome.

regards

Marco

Re: Add the average line to a line chart with multiple lines

Tue, 19 Aug 2014 00:11:30 GMT

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?

Re: Add the average line to a line chart with multiple lines

Anonymous — Thu, 01 Dec 2016 13:40:44 GMT

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