If statement not working correctly???

[jerry_ile]

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

PO	Transport	KG	Qty	Freight Charge	By Sea	Difference
G2222	S	0.225	360	£48.60	£48.60	£0
G3333	A	0.225	67	£44.47	£9.04	£35
		TOTAL	427	-	£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

[Mark_Little]

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

[sunny_talwar · Accepted solution]

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))

[jerry_ile]

The Variables are

vShipCost = .60

vAirCost = 2.95

[jerry_ile]

Perfect thanks for such a quick reply

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

[sunny_talwar]

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