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
