3 Replies Latest reply: Oct 23, 2018 11:04 AM by Sunny Talwar

# If statement not working correctly???

Hi,

I have an if statement in a table measure that calculates a shipping weight against a shipping cost from a variable

It seems to work in the table although it doesn't show a total

If(Transport = 'S',

(vShipCost / (1/KG)) * sum(Qty),

If(Transport = 'A',

(vAirCost / (1/KG)) * sum(Qty),

(vLandCost / (1/KG)) * sum(Qty)))

Also if I put it in a KPI and minus By Sea to show the amount lost by Air shipment, it ignores the transport and multiplys everything by Air

vShipCost = .60

vAirCost = 2.95

POTransportKG
G2222S0.225360£48.60£48.60£0
G3333A0.22567£44.47£9.04£35
TOTAL427-£57.64

Note By sea field has (vShipCost / (1/KG)) * sum(Qty) and shows a total but Freight Charge using the if statement above doesn't show total

Can you please advise on how to correct the if statement for the table and KPI

• ###### Re: If statement not working correctly???

Hi,

Hard to say exactly not knowing the data structure or whats in the variables.

But i would start by changing the total setting on the expression from auto to Sum.

See if this corrects your value.

Mark

• ###### Re: If statement not working correctly???

The Variables are

vShipCost = .60

vAirCost = 2.95

• ###### Re: If statement not working correctly???

Try this

Sum(Aggr(

If(Transport = 'S',

(vShipCost / (1/KG)) * sum(Qty),

If(Transport = 'A',

(vAirCost / (1/KG)) * sum(Qty),

(vLandCost / (1/KG)) * sum(Qty)))

, Transport))

• ###### Re: If statement not working correctly???

Perfect thanks for such a quick reply

Can you let me know why Aggr makes the difference and gets this working please?

• ###### Re: If statement not working correctly???

KPI object doesn't have a dimension... so when you do If(Transport = ....) there are multiple possible values of Transport and it just couldn't find a single one and errors out. By adding Aggr() with Transport, I told the expression to create a virtual table with Transport as dimension and then perform the calculation. Same is true for the totals