amyfb
New member
- Joined
- Dec 18, 2020
- Messages
- 24
- Reaction score
- 0
- Points
- 1
- Location
- Southeastern PA, USA
- Excel Version(s)
- O365
I can't get the last step with an "if/then/else construct" to return correct values.
I've proved that the Rate can be multiplied by the Per Case column. But I can't get that multiplication to execute in the If/then statement. The text (UOM) has been trimmed, I checked the spelling of the criteria, and the data types are all correctly set. I'm out of ideas and have written that If/then in every way I could think of but it doesn't do the math.
Any clues from the wizards here?
thanks
amyfb
I've proved that the Rate can be multiplied by the Per Case column. But I can't get that multiplication to execute in the If/then statement. The text (UOM) has been trimmed, I checked the spelling of the criteria, and the data types are all correctly set. I'm out of ideas and have written that If/then in every way I could think of but it doesn't do the math.
Any clues from the wizards here?
thanks
amyfb
Code:
let
Source = Table.NestedJoin(commissionWdateParam, {"SKU"}, internalLookup, {"ITEM_CODE"}, "internalLookup", JoinKind.LeftOuter),
#"Expanded internalLookup" = Table.ExpandTableColumn(Source, "internalLookup", {"UOM", "DF", "FF", "T9", "T9-calculated", "PER_CASE", "TFC", "WEIGHT", "LTFC"}, {"UOM", "DF", "FF", "T9", "T9-calculated", "PER_CASE", "TFC", "WEIGHT", "LTFC"}),
#"change type (factors to decimal)" = Table.TransformColumnTypes(#"Expanded internalLookup",{{"DF", type number}, {"FF", type number}, {"T9", type number}}),
#"calc newLTFC" = Table.AddColumn(#"change type (factors to decimal)", "newLTFC", each ( ( ([WEIGHT] * 5.04) - ([WEIGHT] * [FF]) * 1.17) + [LTFC])),
#"calc newT9" = Table.AddColumn(#"calc newLTFC", "newT9", each [newLTFC] * [T9]),
#"Changed Type" = Table.TransformColumnTypes(#"calc newT9",{{"newLTFC", type number}, {"newT9", type number}}),
#"calc threshold for newComm" = Table.AddColumn(#"Changed Type", "Threshold", each (([newT9] - [newLTFC]) / 2) + [newLTFC]),
#"calc newComm" = Table.AddColumn(#"calc threshold for newComm", "newComm", each if [RATE] > [newT9] then "Full Commission" else
if [RATE] < [newLTFC] then "No Commission" else if [RATE] > [newLTFC] and [RATE] < [Threshold] then "1% Commission" else if [RATE] > [Threshold] and [RATE] < [newT9] then "Half Commission" else "check"),
#"add commission change alert" = Table.AddColumn(#"calc newComm", "FlagCommissionChange", each if [currComm] <> [newComm] then "change alert" else null),
#"Reordered Columns" = Table.ReorderColumns(#"add commission change alert",{"SKU", "ITEM_SHORT_NAME", "PRICE_LIST", "commission", "t9 uom", "ltfc uom", "RATE", "PRICE_LIST_RATE", "oldThreshold", "UOM", "DF", "FF", "T9", "T9-calculated", "PER_CASE", "TFC", "WEIGHT", "LTFC", "newLTFC", "newT9", "Threshold", "currComm", "newComm", "FlagCommissionChange"}),
#"calc SM % change" = Table.AddColumn(#"Reordered Columns", "SM % change", each (( [RATE] - [newT9] ) / [RATE]) - (([RATE] - [#"T9-calculated"] )/ [RATE])),
#"calc GM % change" = Table.AddColumn(#"calc SM % change", "GM % change", each (( [RATE] - [newLTFC]) / [RATE]) - (([RATE] - [LTFC] )/ [RATE])),
#"rate times per case to proof" = Table.AddColumn(#"calc GM % change", "rateperCaseCustom", each [RATE] * [PER_CASE]),
#"Changed Type1" = Table.TransformColumnTypes(#"rate times per case to proof",{{"rateperCaseCustom", type number}}),
#"Trimmed Text" = Table.TransformColumns(#"Changed Type1",{{"UOM", Text.Trim, type text}}),
trytofixrate = Table.AddColumn(#"Trimmed Text", "fixtherateCustom", each if [UOM] = "Each" then [RATE] * [PER_CASE] else if [UOM] <> "Each" then [RATE] * 1 else "")
in
trytofixrate
Last edited by a moderator: