topic Re: column total not calculated correct with an if expression in QlikView

column total not calculated correct with an if expression

chriscools — Tue, 31 Jan 2017 22:32:08 GMT

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

Re: column total not calculated correct with an if expression

pljsoftware — Wed, 01 Feb 2017 00:58:02 GMT

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

Re: column total not calculated correct with an if expression

chriscools — Thu, 09 Feb 2017 20:43:52 GMT

Hello,

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

thank you for your help!!!

grtz,

chris