Ciao!
is there any way to calculate how many returns have come in using sales from the 3rd week (not Culmative)
For Example. Customer orders item A on 28th Nov 2011, then on the 12th he returns it saying it is faulty.
how do I calculate the formula to work out percentage from number of qty returned of Item A and dividing number of qty ordered from the 3rd Week?
I tried this formula :
Case when {account} = '1100 Sales' and ({today} - {trandate}) BETWEEN 0 AND 5 then decode({type}, 'Sales Order' , nvl({quantity},'0'),0) + decode({type}, 'Return Authorisation', nvl({quantity},'0'),0) else 0 end
but this only gives me how many qty is been returned...I tried using this two formula's (see below) but keep getting invalid expression...
Case when ({account} = '1100 Sales' and ({today} - {trandate}) BETWEEN 0 AND 5 then decode({type}, 'Sales Order' , nvl({quantity},'0'),0) + decode({type}, 'Return Authorisation', nvl({quantity},'0'),0) * 100) / decode({type}, 'Sales Order' , nvl({quantity},'0'),0) else 0 end
&
Case when ({account} = '1100 Sales' and ({today} - {trandate}) BETWEEN 0 AND 5 then decode({type}, 'Sales Order' , nvl({quantity},'0'),0) + decode({type}, 'Return Authorisation', nvl({quantity},'0'),0) * 100) / decode({type}, 'Sales Order' , nvl({quantity},'0'),0) + decode({type}, 'Currency Revaluationt', nvl({quantity},'0'),0) else 0 end
I do not know if there is any other way of getting the percentage of sales from the 3rd Week..but I want it automated so I always looks at the last week (14 to 21) - I tried doing it this way (two case statements) but still it fails :h_a_w:
Case when ({account} = '1100 Sales' and ({today} - {trandate}) BETWEEN 0 AND 7 then decode({type}, 'Sales Order' , nvl({quantity},'0'),0) + decode({type}, 'Return Authorisation', nvl({quantity},'0'),0) * 100) else 0 end / case WHEN ({trandate}) BETWEEN 14 AND 21 then decode({type}, 'Sales Order' , nvl({quantity},'0'),0) + decode({type}, 'Currency Revaluationt', nvl({quantity},'0'),0) else 0 end
Any help appreciated..