Hi All,
I am having straight table chart and i have fetched campaign and email send date as dimensions and i have 5 expression and 5 color coding expressions.
First i need to show only 10 records from recent to last 10 dates(sorted should be done by email send date). For that i have went to presentation and in max number i have given max number is 10.
Now my requirement is I have UNIQUE OPEN RATE and the color coding formula i have given is
If(((sum([Unique Opens])/sum([Total Sends])) -
(Sum( Total Aggr(sum([Unique Opens])/sum([Total Sends]),Campaign,[Email Sent Date]))/ Count( Total Aggr(sum([Unique Opens])/sum([Total Sends]),Campaign,[Email Sent Date]))))
/(Sum( Total Aggr(sum([Unique Opens])/sum([Total Sends]),Campaign,[Email Sent Date]))/ Count( Total Aggr(sum([Unique Opens])/sum([Total Sends]),Campaign,[Email Sent Date]))) *100 <-10,
'qmem://<bundled>/BuiltIn/led_r.png',
If(((sum([Unique Opens])/sum([Total Sends])) -
(Sum( Total Aggr(sum([Unique Opens])/sum([Total Sends]),Campaign,[Email Sent Date]))/ Count( Total Aggr(sum([Unique Opens])/sum([Total Sends]),Campaign,[Email Sent Date]))))
/(Sum( Total Aggr(sum([Unique Opens])/sum([Total Sends]),Campaign,[Email Sent Date]))/ Count( Total Aggr(sum([Unique Opens])/sum([Total Sends]),Campaign,[Email Sent Date]))) *100 >10,
'qmem://<bundled>/BuiltIn/led_g.png','qmem://<bundled>/BuiltIn/led_y.png'))
If the value is less than 10 percent means redcolor , greater than 10 percent means green and in between means yellow color.
And the for UNIQUE CTR the formula is
If(((sum([Unique Clickthroughs])/sum([Total Sends])) -
(Sum( Total Aggr(sum([Unique Clickthroughs])/sum([Total Sends]),Campaign,[Email Sent Date]))/ Count( Total Aggr(sum([Unique Clickthroughs])/sum([Total Sends]),Campaign,[Email Sent Date]))))
/(Sum( Total Aggr(sum([Unique Clickthroughs])/sum([Total Sends]),Campaign,[Email Sent Date]))/ Count( Total Aggr(sum([Unique Clickthroughs])/sum([Total Sends]),Campaign,[Email Sent Date]))) *100 <-5,
'qmem://<bundled>/BuiltIn/led_r.png',
If(((sum([Unique Clickthroughs])/sum([Total Sends])) -
(Sum( Total Aggr(sum([Unique Clickthroughs])/sum([Total Sends]),Campaign,[Email Sent Date]))/ Count( Total Aggr(sum([Unique Clickthroughs])/sum([Total Sends]),Campaign,[Email Sent Date]))))
/(Sum( Total Aggr(sum([Unique Clickthroughs])/sum([Total Sends]),Campaign,[Email Sent Date]))/ Count( Total Aggr(sum([Unique Clickthroughs])/sum([Total Sends]),Campaign,[Email Sent Date]))) *100 >5,
'qmem://<bundled>/BuiltIn/led_g.png', 'qmem://<bundled>/BuiltIn/led_y.png'))
Here if the value is less than 5 percent means red, greater 5 means green and in between means yellow.
I don't know where i am missing .
Can you please help.
Attached qvw and excel for reference.
