Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
My data looks like this.
Customer No. Base Price Price
A 10 6
B 20 14
Z -15
C 30 25
Z -10
The Report should look like this
Customer Final Price
A 1
B 4
C 15
The formula to calculate the final price looks like this.
Z is the customer which represents the discount given to all the customer who appears before Z on prorate basis. Like in this example, the first discount of the first Z ($15) will be applied to A and B on a prorated basis. The discount of the second Z ($10) will be applied to C.
So to calculate the final price of A.
(Price of A / Total price of A + B ) / 100:
($10 / $10 + $20 ) / 100 = 33.3%
33.3% of $15 = $5
Likewise, the paid price of B would be 4 and the final price of C would be 15.
Any idea?
Thanks.
Hi,
If scripting is an option you can do a preceding load using peek function to add a group column;
data: Load If(RowNo()=1, 1, If(peek([Customer No.])='Z', peek(GRP)+1, peek(GRP) ) ) AS GRP, RowNo() As RowNumber, * ; LOAD * INLINE [ Customer No., Base Price, Price A, 10, 6 B, 20, 14 Z, , -15 C, 30, 25 Z, , -10 ];
You can then left join both your sum of base price by group;
left join (data) load GRP, Sum([Base Price]) as SUMBP resident data group by GRP;
And the discount in the Z row;
left join (data) load GRP, Price as Z_Price resident data where [Customer No.]='Z';
I think you then have all the numbers you need to do the calculation on each row;
Cheers,
Chris.