Need to calculate avg

[bharatkishore]

Hi All

I and dimension called A have written an expression for calculating a metric called unique contact. The expression is

=(sum([Total Sends])-sum([Total Unsubscribes]))-sum([Total Bouncebacks])

By writing this i have got a value for example say 1799.

By doing avg of that whole 10 values i got 6003.

Now  i need to calculate the avg which is like 1799/6003.=29.97

Can you please tell me how  can i achive this. 

Attached excel for more understanding

[sunny_talwar]

Can you create a third expression with this and see what you get?

Avg(TOTAL Aggr((Sum([Total Sends]) - Sum([Total Unsubscribes])) - Sum([Total Bouncebacks]), Name))

[bharatkishore]

I have given the expression.. Attached image for your reference..

[sunny_talwar]

Not sure if making this change will make any difference, but try this:

=((Sum([Total Sends]) - Sum([Total Unsubscribes])) - Sum([Total Bouncebacks]))

/

Avg(TOTAL Aggr(((Sum([Total Sends]) - Sum([Total Unsubscribes])) - Sum([Total Bouncebacks])), [Email Name]))

[bharatkishore]

I have given sunny.. Please find the attached qvw

[sunny_talwar · Accepted solution]

You have dimension limits. Try this

=((Sum([Total Sends]) - Sum([Total Unsubscribes])) - Sum([Total Bouncebacks]))

/

Avg(TOTAL {<[Email Name] = {"=Rank(((Sum([Total Sends]) - Sum([Total Unsubscribes])) - Sum([Total Bouncebacks]))) <= 10"}>} Aggr((Sum([Total Sends]) - Sum([Total Unsubscribes])) - Sum([Total Bouncebacks]), [Email Name]))

[bharatkishore]

Thanks a lot..It worked like a gem... i just have one more query... If the value crosses more that 25 it should be in red color and if the value is less than 25 it should be in green color.. Can you please tell me how to achieve this.

[sunny_talwar]

Something like this?

Expression used in the background color expression:

=if(Column(2) > 0.25, LightRed(), LightGreen())