2 Replies Latest reply: Feb 23, 2015 4:16 PM by Ross Luciano RSS

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

    Ross Luciano

      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