Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
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
Highlighted
Master II
Master II

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

put  UnitsSold >= AvgUnitsSold AND  UnitMargin >= AvgUnitMargin

View solution in original post

Highlighted
Contributor II
Contributor II

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