Forum Discussion

Robert1981's avatar
Robert1981
Helper III
2 years ago
Solved

Need help with IF/Switch

Hi,   Below is a screenshot from my report. I have an extract from SAP. In the report it gives me all the possible billing quantities. I need to find the right quantities, based on the Sales Unit....
Need Help
  • Ahmedx's avatar
    Ahmedx
    2 years ago

    try to split my measure into 4 type measures
    Invoiced_QTY1, Invoiced_QTY2, Invoiced_QTY3, Invoiced_QTY4

    Invoiced_QTY1 =
VAR _SalesUnit = [SalesUnit]
VAR _t1 = _SalesUnit="KG"
VAR _t2 = _SalesUnit in {"LB", "/LB"}
VAR _t3 = _SalesUnit ="BX"

VAR St_UOM = SWITCH(TRUE(),
 _t1,[InvQtyKG],
 _t2 , [InvQtyE],
 _t3,[InvQtyBX],
_
RETURN
St_UOM
----------------
Invoiced_QTY2 =
VAR _SalesUnit = [SalesUnit]
VAR _t4 = _SalesUnit ="CT"
VAR _t5 = _SalesUnit ="SM"
VAR _t6 = _SalesUnit ="LT"

VAR St_UOM = SWITCH(TRUE(),
_t4,[InvQtyCT],
_t5,[InvQtySM],
_t6,[InvQtyLTR],
RETURN
St_UOM
-----------------
Invoiced_QTY3 =
VAR _SalesUnit = [SalesUnit]
VAR _t7 = _SalesUnit ="PF"
VAR _t8 = _SalesUnit ="RL"
VAR _t9 = _SalesUnit in {"DR", "KT","EA"}
VAR REVENUE = [InvGrossAnrtDocCurr]
VAR St_UOM = SWITCH(TRUE(),
_t7,[InvQtyPF],
_t8,[InvQtyRL],
_t9,[InvQtyEA],
RETURN
St_UOM
------------------
Invoiced_QTY4 =
VAR _SalesUnit = [SalesUnit]
VAR _t10 = _SalesUnit in {"GR", "OC","LF","BG","SF","SY","SH","FT","YD","MR"}
VAR St_UOM = SWITCH(TRUE(),
 _t10,[InvQtyS])
RETURN
St_UOM
-------------------------
Invoiced_QTY_Final =
VAR REVENUE = [InvGrossAnrtDocCurr]
RETURN
DIVIDE(REVENUE,[Invoiced_QTY1]+[Invoiced_QTY2]+[Invoiced_QTY3]+[Invoiced_QTY4],0)
Robert1981's avatar
Robert1981
Helper III
2 years ago

Thank you,
Just tried it and I get the same error message:
OLE or ODBC error: We couldn't fold the expression to the data source. Please try a simpler expression..

Ahmedx's avatar
Ahmedx
Super User
2 years ago

try to split my measure into 4 type measures
Invoiced_QTY1, Invoiced_QTY2, Invoiced_QTY3, Invoiced_QTY4

Invoiced_QTY1 =
VAR _SalesUnit = [SalesUnit]
VAR _t1 = _SalesUnit="KG"
VAR _t2 = _SalesUnit in {"LB", "/LB"}
VAR _t3 = _SalesUnit ="BX"

VAR St_UOM = SWITCH(TRUE(),
 _t1,[InvQtyKG],
 _t2 , [InvQtyE],
 _t3,[InvQtyBX],
_
RETURN
St_UOM
----------------
Invoiced_QTY2 =
VAR _SalesUnit = [SalesUnit]
VAR _t4 = _SalesUnit ="CT"
VAR _t5 = _SalesUnit ="SM"
VAR _t6 = _SalesUnit ="LT"

VAR St_UOM = SWITCH(TRUE(),
_t4,[InvQtyCT],
_t5,[InvQtySM],
_t6,[InvQtyLTR],
RETURN
St_UOM
-----------------
Invoiced_QTY3 =
VAR _SalesUnit = [SalesUnit]
VAR _t7 = _SalesUnit ="PF"
VAR _t8 = _SalesUnit ="RL"
VAR _t9 = _SalesUnit in {"DR", "KT","EA"}
VAR REVENUE = [InvGrossAnrtDocCurr]
VAR St_UOM = SWITCH(TRUE(),
_t7,[InvQtyPF],
_t8,[InvQtyRL],
_t9,[InvQtyEA],
RETURN
St_UOM
------------------
Invoiced_QTY4 =
VAR _SalesUnit = [SalesUnit]
VAR _t10 = _SalesUnit in {"GR", "OC","LF","BG","SF","SY","SH","FT","YD","MR"}
VAR St_UOM = SWITCH(TRUE(),
 _t10,[InvQtyS])
RETURN
St_UOM
-------------------------
Invoiced_QTY_Final =
VAR REVENUE = [InvGrossAnrtDocCurr]
RETURN
DIVIDE(REVENUE,[Invoiced_QTY1]+[Invoiced_QTY2]+[Invoiced_QTY3]+[Invoiced_QTY4],0)