topic Re: Need to calculate average in QlikView

Need to calculate average

bharatkishore — Thu, 04 Aug 2016 02:54:36 GMT

Hi All,

I have a straight table chart and in the presentation tab i have given max number as 10 values.

I have campaign and email send date as dimension and my expression is

sum([Unique Clickthroughs])/sum([Total Sends]).

Now by doing this i have got some values.

Now i needed an expression to calculate the avg of those 10 values.

Attached excel for reference and how i am calculating the values.

Thanks

Bharat

Re: Need to calculate average

vishsaggi — Thu, 04 Aug 2016 03:12:41 GMT

May be try this: Change accordingly.

= Num(Avg(Total [Unique Open Rate]), '#,##0.00%')

Re: Need to calculate average

bharatkishore — Thu, 04 Aug 2016 03:15:15 GMT

Tried.. but no value is coming..

Re: Need to calculate average

vishsaggi — Thu, 04 Aug 2016 03:17:42 GMT

Can you share your sample app if possible?

Re: Need to calculate average

bharatkishore — Thu, 04 Aug 2016 03:24:14 GMT

please check the attached qvw and excel which values should come and for the calculation also.

Re: Need to calculate average

vishsaggi — Thu, 04 Aug 2016 03:47:28 GMT

Bharat, I think the values from excel sheet you sent works fine with the initial expression i sent, but there are synthetic keys in your data model. Can you send the actual two excel files you are loading into this qvw file if possible ?

Re: Need to calculate average

bharatkishore — Thu, 04 Aug 2016 03:51:51 GMT

You can remove the second tab in script.

I have tried with the same expression but it is not working for me.

If possible can u please send me the file. so tht i can check

Re: Need to calculate average

vishsaggi — Thu, 04 Aug 2016 04:10:31 GMT

I have just used your excel sheet and the qvw file and it worked.

Please check and let me know.

Re: Need to calculate average

maheshkuttappa — Thu, 04 Aug 2016 04:15:25 GMT

Below expression should work

Num(([Unique Open Rate]-Avg(TOTAL [Unique Open Rate]) )/Avg(TOTAL [Unique Open Rate]),'###.##%')

Re: Need to calculate average

Greg_Williams — Thu, 04 Aug 2016 04:27:30 GMT

Does this address your request?

Perhaps, if not, consider AGGR function, or TOTAL added to your sum or avg function.

-gw

Re: Need to calculate average

bharatkishore — Thu, 04 Aug 2016 04:35:12 GMT

HI Greg,

Can you please tell me how can i calculate using aggr function because my requirement needs that because i have filters called market and brand..

Can you please tell me how to achieve this

Re: Need to calculate average

bharatkishore — Thu, 04 Aug 2016 04:38:27 GMT

This is not working when i select filters.

Attaching qvw pls check..

Re: Need to calculate average

maheshkuttappa — Thu, 04 Aug 2016 05:00:19 GMT

if you do not want filters to effect calculation you can use below expression

Num(([Unique Open Rate]-Avg({1} TOTAL [Unique Open Rate]))/Avg({1}TOTAL [Unique Open Rate]),'###.###%')

Re: Need to calculate average

bharatkishore — Thu, 04 Aug 2016 05:03:56 GMT

No No. I need filters to be effected the calculation.. The values should change based on the selection of filters.

Re: Need to calculate average

sunny_talwar — Thu, 04 Aug 2016 05:18:59 GMT

This?

Expressions:

1) If(Num(Rank(TOTAL If(Sum([Unique Clickthroughs]) > 0, [Email Sent Date]), 4, 1)) <= 10, Sum([Unique Clickthroughs])/Sum([Total Sends]))

If(Num(Rank(TOTAL If(Sum([Unique Clickthroughs]) > 0, [Email Sent Date]), 4, 1)) <= 10,

Avg(TOTAL Aggr(If(Num(Rank(TOTAL If(Sum([Unique Clickthroughs]) > 0, [Email Sent Date]), 4, 1)) <= 10, Sum([Unique Clickthroughs])/Sum([Total Sends])), Campaign, [Email Sent Date])))

3) =(Unique_CTR/AvgValue)-1

Re: Need to calculate average

sunny_talwar — Thu, 04 Aug 2016 05:21:53 GMT

Or may be add these to make the formula even more robust

If(Num(Rank(TOTAL If(Sum([Unique Clickthroughs]) > 0 and Sum([Total Sends]) > 0, [Email Sent Date]), 4, 1)) <= 10, Sum([Unique Clickthroughs])/Sum([Total Sends]))

If(Num(Rank(TOTAL If(Sum([Unique Clickthroughs]) > 0 and Sum([Total Sends]) > 0, [Email Sent Date]), 4, 1)) <= 10,

Avg(TOTAL Aggr(If(Num(Rank(TOTAL If(Sum([Unique Clickthroughs]) > 0 and Sum([Total Sends]) > 0, [Email Sent Date]), 4, 1)) <= 10, Sum([Unique Clickthroughs])/Sum([Total Sends])), Campaign, [Email Sent Date])))

Re: Need to calculate average

bharatkishore — Thu, 04 Aug 2016 05:38:37 GMT

Awesome Sunny. Exactly What i needed.. I don't know for me why this is this much complex and for you its matter of secs.

Now the only thing i need is

for this expression if the value is less than 10(Unique_CTR/AvgValue)-1 percent means i need do display redimage 'qmem://<bundled>/BuiltIn/led_r.png' and more than 10 percent means green and in between means yellow.

Can you please help me how can i achieve this..

Re: Need to calculate average

sunny_talwar — Thu, 04 Aug 2016 05:43:40 GMT

What comes in between less than 10 and more than 10? Yellow = 10%

Re: Need to calculate average

bharatkishore — Thu, 04 Aug 2016 05:46:49 GMT

If the percentage vale is less than -10 percent means red indicator, if the value is +10 means green in between -10 and +10 is yellow.. Sorry i forget to mention -10

Re: Need to calculate average

sunny_talwar — Thu, 04 Aug 2016 05:46:59 GMT

I changed the conditons slightly, but can you modify them based on your requirement.

< 0, Red

< 10%, Yellow

> 10%, Red

If(Num(Rank(TOTAL If(Sum([Unique Clickthroughs]) > 0 and Sum([Total Sends]) > 0, [Email Sent Date]), 4, 1)) <= 10,

If(Change <= 0, 'qmem://<bundled>/BuiltIn/led_r.png',

If(Change <= 0.10, 'qmem://<bundled>/BuiltIn/led_y.png', 'qmem://<bundled>/BuiltIn/led_g.png')))