0 Replies Latest reply: Jul 30, 2013 6:13 AM by Andrew Putz RSS

    Rank expression problem

      Hey all,

       

      Was hoping somebody could help with a formula (see below) it is supposed to give the sum total revenue for the top ten products (sku_id) as defined in the formula (basically where the sport conversion rate (units/visits) is greater than the conversion rate over the past 28 days. it is currently giving me a null value.

       

       

      = '£' &

      num(

      sum(

      {<sku_id= {"=rank(

      sum(aggr(

      IF((sum({$<fulfillment=>}units)/sum({$<fulfillment=>}visits))

      >

      (sum({<date= {"<=$(=Max(date)) >$(=Max(date)-28) "}, fulfillment=>}units)

      /

      sum({<date= {"<=$(=Max(date)) >$(=Max(date)-28) "}, fulfillment=>}visits))

      ,sum({$<fulfillment=>}views),'N/A')

      ,sku_id))

      , sku_id) <=10"}>}

      revenue )

      , '#,###,###')

       

       

      I previously had the formula as:

       

      = '£' &

      num(

      sum(

      {<sku_id= {"=rank(

      sum(aggr(

      IF((sum({$<fulfillment=>}units)/sum({$<fulfillment=>}visits))

      > v_parent_conversion

      ,sku_id))

      , sku_id) <=10"}>}

      revenue )

      , '#,###,###')

       

      which was working, the only difference was I had a variable as the comparator in the IF statement. business rules of changed and the comparator is now at a product level.

       

      This is a real sticking point so if anybody can point out where I am going wrong I would be very grateful!

       

      Thanks,

       

      Andrew