Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
rluciano1012
Contributor II
Contributor II

How can I do an if(and formula in a QlikView expression?

This is driving me crazy.  I have an Excel formula that works perfectly in Excel but I can't figure out how write it in an expression to use in a straight table in QlikView.  The IF statements work fine in the expression, but when I try to do AND, I just get errors.  So I guess my question really is this. How can you do IF(AND functions in QlikView or something that does the same thing?

The gist of what I'm trying to do is restaurant menu engineering based on a recent menu mix of items sold.  For example, IF a menu item is above average in popularity AND has an above average unit margin, then call the menu item a "Star", IF an item sells less than average units sold AND has a high unit margin, its a "Puzzle", IF an the item units sold are below average AND has below average unit margin, its a "Dog" and then if none of those are true, the formula would default to "Plowhorse", which would be a high seller but a lower than average unit margin.

This is the EXCEL version of the formula.

=IF(AND( UnitsSold >= AvgUnitsSold, UnitMargin >= AvgUnitMargin), "Star", IF(AND( UnitsSold <= AvgUnitsSold, UnitMargin > AvgUnitMargin), "Puzzle", IF(AND( UnitsSold <= AvgUnitsSold, UnitMargin < AvgUnitMargin), "Dog", "Plowhorse")))

What would be an QlikView expression way to do that?  All help is greatly appreciated!

Ross

1 Solution

Accepted Solutions
m_woolf
Master II
Master II

In place of:   AND( UnitsSold >= AvgUnitsSold, UnitMargin >= AvgUnitMargin)

put  UnitsSold >= AvgUnitsSold AND  UnitMargin >= AvgUnitMargin

View solution in original post

2 Replies
m_woolf
Master II
Master II

In place of:   AND( UnitsSold >= AvgUnitsSold, UnitMargin >= AvgUnitMargin)

put  UnitsSold >= AvgUnitsSold AND  UnitMargin >= AvgUnitMargin

rluciano1012
Contributor II
Contributor II
Author

Thanks, Mike.  That worked.  Not sure why I didn't think of trying that.  Thanks again!