0 Replies Latest reply: Aug 3, 2016 10:41 AM by Bharat Kishore RSS

    color coding issue

    Bharat Kishore

      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.