If, then and else with Set Analysis

[Dpage]

Hello,

I am attempting to convert a unit of measure, 'ml' to 'fl.oz' by dividing the qty. by 30. This works perfectly using an If statement. An example would be If([ItemInventoryAssemblyComponent.Unit Of Measure Unit Full Name]='Milliliter',sum({<[Transactions.Txn Type]={'BuildAssembly'}, Transactions.Pending={1}>}[Transactions.Quantity With Sign]*ItemInventoryAssemblyComponent.Quantity/30),
sum({<[Transactions.Txn Type]={'BuildAssembly'}, Transactions.Pending={1}>}[Transactions.Quantity With Sign]*ItemInventoryAssemblyComponent.Quantity)). This gives me the correct sum, mls converted to fl.oz and other items based on their assigne U/M.

However, when I add a variable to calculate avg. usage over the last 12 months, the second 'else' sum function is grayed out (instead of blue) and doesn't calculate. If I remove the variable in the first sum function, it's blue again and will calculate but won't account for the variable "vLast12MonthsRolling". The point of the column is to show avg. use over time so I need the variable but the Items that are measured in milliliters are easier to understand as fl.oz. Any ideas why I can't have an 'else' sum function with the same variable for both?The first column calculates correctly as fluid oz for Qty. Required... The second column is still calculating mls.

The above expression gives me the calculations for the table shown. This is correct in mls but I want to convert to fl.oz and show everything else as is (with their assigned U/M)

[rubenmarin · Accepted solution]

Hi, it doesn't shows blue becuase the syntax checker marks an error after "$(vLast12MonthsRolling)>}", I see this as an error of the syntax checker, becasuse this kinnd of espressions can be ok and working, but syntax cheecker still marks an error so don't worry about this. 

Note that it still says "Expression OK", this usually is more accurate but still it can't check the expanded value of $(vLast12MonthsRolling).

I would try with each expression in it's own collumn, debug expressions separatedly and when both are working ok merge them in final 'if' expression.

[Dpage]

Thank you. That appears to work. Still have to run some test data. I should have followed it through. I did notice the "Expression Ok" at the top. Thank you for the explanation.