how to deal null, negative and zero values

[Anonymous]

Hi,

I've to calculate a sales percentage like this:   (Current_year_sales  -  Previous_year_sales) / Previous_year_sales

My problem is how to manage null values and/or zero. For example, how can I check if Previous_year_sales is zero or negative or null ?

The same for Current_year_Sales of course.

How can I deal this situation in order to display the right percentage value ?

Thanks in advance

[IAMDV]

Hi,

You can use functions like ISNULL() to check NULL values. But in your case you have three conditions and I'd do something like this...

IF(Current_year_sales > 0 AND Previous_year_sales > 0,

      (Current_year_sales  -  Previous_year_sales) / Previous_year_sales,

    Whatever you wanted to show when the condition is false)

Ideally you should tag the NULLS in the script so that you can handle them easily.

Cheers,

DV

www.QlikShare.com