topic null values in QlikView

null values

Anonymous — Sun, 04 Nov 2012 19:00:03 GMT

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 ?

Thanks in advance

Re: null values

swuehl — Sun, 04 Nov 2012 20:17:23 GMT

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?

Re: null values

Anonymous — Sun, 04 Nov 2012 20:43:45 GMT

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 ?

Re: null values

swuehl — Sun, 04 Nov 2012 20:48:24 GMT

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.

Re: null values

Anonymous — Sun, 04 Nov 2012 21:30:56 GMT

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 ?