topic Re: How to display 0 when there is no record in QlikView

How to display 0 when there is no record

Sat, 27 Apr 2013 09:21:21 GMT

Hi,

I create a straight table to show the sales of each month, dimension is from April to September, expression is SUM(Sales). The source data is

Customer	Sales	Date
cust1	10	4/10/2013
cust1	20	5/1/2013
cust2	20	4/20/2013

Now the straight table can only show April and May and the correspinding sales, I want other months to display as well with sales 0, how can I do this?

Thanks in advance for your help.

Re: How to display 0 when there is no record

nilesh_gangurde — Sat, 27 Apr 2013 15:51:48 GMT

Hiii julialiu,

In the presentation tab => UnCheck Supress Zero Values.

you will get all data including Zero sales.

-Nilesh

Re: How to display 0 when there is no record

amien — Sat, 27 Apr 2013 22:50:56 GMT

Dear Julialiu,

unchecking supress zero values is not going to work because these dates proberbly don't have any record, so not even a 0.

Please read this thread.

http://community.qlik.com/docs/DOC-3786

Re: How to display 0 when there is no record

Sun, 28 Apr 2013 01:12:52 GMT

Hi Nilesh,

The Supress Zero Values is unchecked in my above case. Because there is no record in other months, the sales is not calculated as 0 with expression SUM(Sales), instead there is no value at all.

Re: How to display 0 when there is no record

nilesh_gangurde — Sun, 28 Apr 2013 09:45:41 GMT

Hii julialiu,

Ohhkk you can add sales as 0 or Null at load time.

for that your script will be like this.

load

Customer,

Sales,

if(isnull(Sales) or Sales = '' or len(Sales) = 0,0,Sales) as SalesField,

Date

From Source;

You can replace the 0 with null() for null values.

you can uncheck the "Suppress when value is null" in the Dimension Tab.

-Nilesh

Re: How to display 0 when there is no record

Thu, 02 May 2013 08:42:32 GMT

Thank you Amien, the doc is very helpful.

Re: How to display 0 when there is no record

Thu, 02 May 2013 08:50:32 GMT

Hi Nilesh,

Thanks for your help, I did it by Join the Calendar table and the Source table, and give some value to the NULL field.

And just FYI, uncheck "Suppress when value is null" will not do the trick.

Re: How to display 0 when there is no record

Thu, 02 May 2013 08:55:03 GMT

in your expression write this code

if(Sales=' ', '0',Sales)

Re: How to display 0 when there is no record

er_mohit — Thu, 02 May 2013 09:02:09 GMT

see the attached file

Re: How to display 0 when there is no record

sujeetsingh — Thu, 02 May 2013 11:16:03 GMT

Try like this:

if(isnull(Sales)<>0,Sales)