The right negative

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.