Pivot table question

[jduluc12]

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.

1. Find prorated % of A and B:

(Price of A / Total price of A + B ) / 100:

($10 / $10 + $20 ) / 100 = 33.3%

1. Find the share of discount of A 

33.3% of $15 =  $5

1. Paid price of A = $6 - $5 = $1

Likewise, the paid price of B would be 4 and the final price of C would be 15.

Any idea?

Thanks.

[chrismarlow]

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.