I recently had a situation where a customer was not happy with the values returned in a budget variance column.
Simple calculation for variance - (Spend - Budget)/Budget
However, in some of their profit centers there was no budget but there was spend! I.e.: (Spend)/0.
Rather than just display a "-" or 0, they wanted this to display as -100%, to really flag it up.
Following on from my previous post about the RangeMax function in QlikView, I thought about it but then, deciding that wouldn't work for me here, I decided on the Alt function.
The Alt function is quite useful because, like RangeMax, you give it a series of Expressions but what it does is returns you the first one that results in a valid number. So, if I use:
Alt((Spend-Budget)/Budget, -1)
When the variance percentage is Null (when Budget is 0), then the value returned will be -1 (-100%). Exactly what I needed.
Alt can also be useful where you have mixed date formats in a date stream - using multiple Date# functions in an Alt and one of them should give you a number.
