Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
cancel
Showing results for 
Search instead for 
Did you mean: 
Dpage
Contributor III
Contributor III

If, then and else with Set Analysis

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?Capture6.PNGThe first column calculates correctly as fluid oz for Qty. Required... The second column is still calculating mls.The first column calculates correctly as fluid oz for Qty. Required... The second column is still calculating mls.Capture8.PNG

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)

Labels (4)
1 Solution

Accepted Solutions
rubenmarin

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.

 

 

View solution in original post

2 Replies
rubenmarin

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.

 

 

View solution in original post

Dpage
Contributor III
Contributor III
Author

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.