topic Re: Average in chart in QlikView

Average in chart

Anonymous — Wed, 16 Apr 2014 17:56:06 GMT

Hi folk,

I have a chart with 1 dimension Campaign.

I want to show difference between each given Campaign and Average of these Campaign (without None).

How can I get average in the chart.

Please see my file.

Re: Average in chart

whiteline — Wed, 16 Apr 2014 18:15:43 GMT

Hi.

Could you describe what do you mean by 'average', since none of expressions provided is definitely clear.

Re: Average in chart

Anonymous — Wed, 16 Apr 2014 18:30:03 GMT

Sum of each campaign / count these campaign.

Other words

6%+6%+4%+2%=18%

18%/4=4.5% -------------average of Campaign

And then I need each given % of Campaign - Average of Campaign

6%-4.5=1.5% - Campaign A

6%-4.5=1.5% - Campaign B

4%-4.5%=-0.5% - Campaign C

2%-4.5%=-2.5% - Campaign D

Thanks,

Re: Average in chart

Wed, 16 Apr 2014 18:37:37 GMT

avg is usually sum of some value by number of elements.

You trying to sum Campaing. But Capaign it is string in your data!

Re: Average in chart

Anonymous — Wed, 16 Apr 2014 18:51:31 GMT

Yes, Campaign is string variable. I got percentage for each campaign, but I don't know how to get sum of my percentage for each campaign.

Re: Average in chart

whiteline — Wed, 16 Apr 2014 18:52:03 GMT

Lets do it a bit carefully.

Campaign	Ratio	Count({<Campaign=-{'None'}>} Campaign)	Count(TOTAL Campaign)
	4,63%	345	1862
Campaign A	5,80%	108	1862
Campaign B	6,02%	112	1862
Campaign C	4,46%	83	1862
Campaign D	2,26%	42	1862

You can see that 4,63% = Average(5.8%, 6.02%, 4.46%, 2.26%) = .. = (108+112+83+42)/1862/4

So to get the average use this expression:

Count({<Campaign=-{'None'}>} total Campaign)/Count(TOTAL Campaign)/Count({<Campaign=-{'None'}>} distinct total Campaign)

Re: Average in chart

Anonymous — Wed, 16 Apr 2014 19:01:14 GMT

Yes, Thanks my friend

Re: Average in chart

Wed, 16 Apr 2014 19:01:33 GMT

I think whiteline showed you solution. If you will need more help, let us know.

regards

Darek

Re: Average in chart

Anonymous — Wed, 16 Apr 2014 19:03:54 GMT

Thanks for helping

Re: Average in chart

maxgro — Wed, 16 Apr 2014 19:10:09 GMT

I choose another field in your data; if my idea is correct, adapt to your requirement

(

sum({<Campaign=-{None}>} [Purchase Horizon Score] )

/ Count({<Campaign=-{None}>}Campaign)

)

(

sum(TOTAL {<Campaign=-{None}>} [Purchase Horizon Score] )

/ Count(total {<Campaign=-{None}>} Campaign)

)