Pivot table question

jduluc12 — Sat, 16 Nov 2024 04:08:03 GMT

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.

Find prorated % of A and B:

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

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

Find the share of discount of A

33.3% of $15 = $5

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.

Re: Pivot table question

chrismarlow — Tue, 19 Mar 2019 20:26:57 GMT

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.

topic Pivot table question in QlikView

Pivot table question

Re: Pivot table question