# New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Not applicable

## null values

Hi,

is it possible to deal null values ?

I've the following pivot

Customer   Sales_CurrentYear     PreviousYear     Difference

A                     1000                         800               200

B                         -                           500               -

For customer B, I've a null value in current year and I can't calculate the difference.

Is there a function to manage this problem ?

4 Replies
Not applicable

## Re: null values

How do you want to define the difference, if Sales_CurrentYear is unknown? Set the sales to zero?

IMHO, returning NULL in case of a unknown value is ok.

What expressions are you using?

Not applicable

## Re: null values

I would compare the sales performance between the current year and year-1.

In the table above,   the sales amount for Customer B in the current year is zero, it means a bad performance:

as value is -500  and in percentage we can consider -100%. How can I manage this null values ?

Not applicable

## Re: null values

You are using three expressions in your pivot chart, right? How do they look like?

edit: if you are not already doing so, use an aggregation function like sum in your sales expressions.

Not applicable

## Re: null values

Concerning total sales:

sum({<MY_YEAR={\$(=Only(MY_YEAR))}, MY_MONTH= {"=\$(=only(SALES_MONTH))"}>} GAINED_AMOUNT    )

Concerning percentage

= IF (ISNULL(sum({<MY_YEAR={\$(=Only(MY_YEAR))}, MY_MONTH= {"=\$(=only(SALES_MONTH))"}>} GAINED_AMOUNT    ) /sum({<MY_YEAR={\$(=Only(MY_YEAR))}, MY_MONTH= {"<=\$(=only(SALES_MONTH))"}>} VALOREACQM    )),0,sum({<MY_YEAR={\$(=Only(MY_YEAR))}, MY_MONTH= {"=\$(=only(SALES_MONTH))"}>} GAINED_AMOUNT    )/sum({<MY_YEAR={\$(=Only(MY_YEAR))}, MY_MONTH= {"<=\$(=only(SALES_MONTH))"}>} VALOREACQM    ))

In my mind, I 'm checking if the sum(.values for customer...)/ sum(...total sales) is null; if yes I put 0, else I do the calculathion.  But is there a better way to manage this issue ?

In order to make more simple the expression, is it possible to use the label (for example Total_sale)  instead of the explicit calculation (sum (.....total sales....) ?

Having an application with three languages (for example English, German and Dutch) may I use a calculated label ?

Community Browser