column total not calculated correct with an if expression

[chriscools]

Hello,

i have a pivot table where the 2 last columns are used to calculate free stock.

"free stock" is to decide if there is too much stock or not enough on sku level.

"free stock not zero" is to calculate the excess stock in total, how much stock do i have that i will end up with if i don't get extra orders.

For this calculation i don't want to take -1000 for reference B into consideration because it would lower the excess stock incorrectly.

for example if i have 2 references and "free stock" for reference A= 1000 en for reference B = -1000,

for "free stock not zero" it should say 0 for reference B .

To do that i used an if statement to check if free stock is lower then zero it should give me a zero, otherwise the amount of free stock.

The expression gives a correct value on the SKU level, but the sum for the colum does not calculate correctly.

Does anybody have an idea how this is possible?

it attached an example.

thanx!

grtz,

chris

[pljsoftware · Accepted solution]

Hi Chris,

I hope to understand correctly your question.

Try this formula

sum(aggr(
if (
(sum ({<ProductStatus = {'Active', 'Einde gamma', 'Sample'}, WarehouseName = {'WHS BE','Kontich'}, LocationFunction={'PICKING','BULK'}>} StockQty * SkuPricePurchaseCurrency )-
(sum ({< ProductStatus = {'Active', 'Einde gamma', 'Sample'}, OrderStatus= {'On hold'},OnHoldReason={'Treated - waiting for client','Treated - waiting for payment','Treated - waiting for payment'}   >}  OrderQtyOpen*SkuPricePurchaseCurrency) + 
sum ({< ProductStatus = {'Active', 'Einde gamma', 'Sample'}, OrderStatus= {'Placed'}  >}  OrderQtyOpen*SkuPricePurchaseCurrency))) <0 ,0,
(sum ({<ProductStatus = {'Active', 'Einde gamma', 'Sample'}, WarehouseName = {'WHS BE','Kontich'}, LocationFunction={'PICKING','BULK'}>} StockQty * SkuPricePurchaseCurrency )-
(sum ({< ProductStatus = {'Active', 'Einde gamma', 'Sample'}, OrderStatus= {'On hold'},OnHoldReason={'Treated - waiting for client','Treated - waiting for payment','Treated - waiting for payment'}   >}  OrderQtyOpen*SkuPricePurchaseCurrency) + 
sum ({< ProductStatus = {'Active', 'Einde gamma', 'Sample'}, OrderStatus= {'Placed'}  >}  OrderQtyOpen*SkuPricePurchaseCurrency)))
)
, ProductGroupLevel1, ProductGroupLevel2, SkuCode))

Regards

Luca Jonathan Panetta

[chriscools]

Hello,

sorry for the late reply, but this  was indeed the answer!

thank you for your help!!!

grtz,

chris