color coding issue

bharatkishore — Wed, 03 Aug 2016 14:41:02 GMT

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.

topic color coding issue in QlikView

color coding issue