28 Replies Latest reply: Aug 4, 2016 7:30 AM by Bharat Kishore

# Need to calculate average

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

May be try this: Change accordingly.

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

• ###### Re: Need to calculate average

Tried.. but no value is coming..

• ###### Re: Need to calculate average

Can you share your sample app if possible?

• ###### Re: Need to calculate average

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

• ###### Re: Need to calculate average

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

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

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

This is not working when i select filters.

Attaching qvw pls check..

• ###### Re: Need to calculate average

Below expression should work

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

• ###### Re: Need to calculate average

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

-gw

• ###### Re: Need to calculate average

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

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

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

This?

Expressions:

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

2)

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

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

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.

• ###### Re: Need to calculate average

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

• ###### Re: Need to calculate average

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

Got it

Try this:

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

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

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

• ###### Re: Need to calculate average

Thank you genious.. Only one query i have i have 3 more expressions in this chart. Can i apply the same logic for the remaining 3 expressions.

• ###### Re: Need to calculate average
I believe you should be able to use a similar logic for showing images. Give it a try and see what happens

• ###### Re: Need to calculate average

Sure and will let you know.. I thank u once again.. thank u so much.

• ###### Re: Need to calculate average

No problem at all .

I only ask for you to mark any helpful responses, as it might help other people in the future looking for answers. Make sure to mark only those responses as useful which were truly helpful.

Thanks,

Sunny

• ###### Re: Need to calculate average

Sure and will do it...

• ###### Re: Need to calculate average

HI Sunny,

I tried for all the expressions and all are working perfectly.

I thank you once again.

Thanks,

Bharat

• ###### Re: Need to calculate average

Thanks Sunny but without you i cannot made to happen. Thank u genius once again.

• ###### Re: Need to calculate average

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