Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

bibopipo
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 ?

Thanks in advance

4 Replies
swuehl
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?

bibopipo
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 ?

swuehl
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.

bibopipo
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 ?