How to display 0 when there is no record

[#1]

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.

[nilesh_gangurde]

Hiii julialiu,

In the presentation tab => UnCheck  Supress Zero Values.

you will get all data including Zero sales.

-Nilesh

[amien · Accepted solution]

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

[#4]

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.

[nilesh_gangurde]

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.

OR

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

-Nilesh

[#6]

Thank you Amien, the doc is very helpful.

[#7]

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.

[#8]

in your expression write this code

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

[er_mohit]

see the attached file

[sujeetsingh]

Try like this:

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