Calculated field in Pivot Table

[niam07]

Hi ya,

I have been searching all through the forum but cannot get my head around Pivot Table calculated field, will be very appreciate of any help. I have raw data as following. I buy stock from different suppliers. Every item has a code. If I buy same stock from different suppliers, my code remains same. My category determines whether I am buying or selling stock.


[TABLE="width: 500"]
<tbody>[TR]
[TD]Suppliers or client[/TD]
[TD]Code
[/TD]
[TD]Category[/TD]
[TD]Item[/TD]
[TD]Quantity[/TD]
[/TR]
[TR]
[TD]Supplier 1[/TD]
[TD]1001[/TD]
[TD]Purchase[/TD]
[TD]Sofa[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]Supplier 1[/TD]
[TD]1002[/TD]
[TD]Purchase[/TD]
[TD]Bed[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]Client 1[/TD]
[TD]1001[/TD]
[TD]Sell[/TD]
[TD]Sofa[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Supplier 2[/TD]
[TD]1001[/TD]
[TD]Purchase[/TD]
[TD]Sofa[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Client 2[/TD]
[TD]1001[/TD]
[TD]Sell[/TD]
[TD]Sofa[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Client 3[/TD]
[TD]1002[/TD]
[TD]Sell[/TD]
[TD]Bed[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

I need Pivot table summary as following where it gives me the balance of my remaining stock.

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Code
[/TD]
[TD]Sum of Purchase Quantity[/TD]
[TD]Stock Remaining[/TD]
[/TR]
[TR]
[TD]Sofa[/TD]
[TD]1001[/TD]
[TD]20[/TD]
[TD]16[/TD]
[/TR]
[TR]
[TD]Bed[/TD]
[TD]1002[/TD]
[TD]30[/TD]
[TD]29[/TD]
[/TR]
</tbody>[/TABLE]

Thanks a lot.

Naim

 

[niam07]

Thanks a lot Sandy, finally able to make that change, add a column and it works. When I have both purchase and sell, the on stock shows difference of both. But when I have only purchase quantity and sales quanity as blank, my on stock column also shows blank. How do I fix that issue please? Thanks again. I am getting table as below.


[TABLE="width: 263"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Item[/TD]
[TD]Code[/TD]
[TD]Purchase[/TD]
[TD]Sales[/TD]
[TD]OnStock[/TD]
[/TR]
[TR]
[TD]L Sofas[/TD]
[TD="align: right"]1001[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]38[/TD]
[/TR]
[TR]
[TD]Tea Table[/TD]
[TD="align: right"]1003[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sleepwell Mattress[/TD]
[TD="align: right"]1007[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bed[/TD]
[TD="align: right"]1002[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Dinning Table[/TD]
[TD="align: right"]1005[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Muda[/TD]
[TD="align: right"]1004[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Divan[/TD]
[TD="align: right"]1006[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Niam

 

[sandy666]

Change in Applied Steps step: Added Custom (via small gear on the end of step) to: if [Sales] = null then [Purchase] else [Purchase]-[Sales]

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Item[/td][td=bgcolor:#70AD47]Code[/td][td=bgcolor:#70AD47]Purchase[/td][td=bgcolor:#70AD47]Sales[/td][td=bgcolor:#70AD47]OnStock[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Bed[/td][td=bgcolor:#E2EFDA]

1002​

[/td][td=bgcolor:#E2EFDA]

2​

[/td][td=bgcolor:#E2EFDA]

1​

[/td][td=bgcolor:#E2EFDA]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Dinning Table[/td][td]

1005​

[/td][td]

5​

[/td][td]

2​

[/td][td]

3​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Divan[/td][td=bgcolor:#E2EFDA]

1006​

[/td][td=bgcolor:#E2EFDA]

2​

[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA]

2​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Mattress[/td][td]

1004​

[/td][td]

4​

[/td][td][/td][td]

4​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Office Chair[/td][td=bgcolor:#E2EFDA]

1007​

[/td][td=bgcolor:#E2EFDA]

5​

[/td][td=bgcolor:#E2EFDA]

5​

[/td][td=bgcolor:#E2EFDA]

0​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Sofas[/td][td]

1001​

[/td][td]

69​

[/td][td]

3​

[/td][td]

66​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Tea Table[/td][td=bgcolor:#E2EFDA]

1003​

[/td][td=bgcolor:#E2EFDA]

3​

[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA]

3​

[/td][/tr]
[/table]
example file post#8

is that what you want?

 

[niam07]

Yes, it works. Thanks again Sandy. You have saved lots of time from my work. Truly appreciated.

 

[sandy666]

You are welcome