Show date Dates with 0 value if data not available

[raju_salmon]

Hi,

I have two tables;

Fact:

Sales Amount,

Date

Calendar:

Date,

Month,

Year

I have created table daily sales, since sales amount is not there on few date, those data are not coming on my trend report.

But i need to show those dates with Zero values.

Can you please suggest anyone.

Thanks.

[vikasmahajan]

Qlikview Can Show only available records it omits null records or where records are not there will not display in charts.

HTH

Vikas

[Mark_Little]

Hi,

Take a look at null as value function within the script.

Mark

[raju_salmon]

I have tried this option, but however even NULL values are not available on that particular date.

[raju_salmon]

Can any one help is there any way to do this?

[Mark_Little]

HI,

Yer you would need to populate them with values,

You could try a resident load after you have the calenday.

Something Like

Noconcatenate

NewData:

Load

     Date

Resident Calendar

Left Join (NewData)

Load

Date,

IF(LEN(Sales)=0, 0, Sales)     AS Sales

...

Resident Table;

Then drop your original.

Mark

[beck_bakytbek]

Hi Raju,

You can use the functin (mentioned above by Mark Little) or

you can try this function:

Noconcatenate

NewData:

Load

     Date

Resident Calendar

Left Join (NewData)

Load

Date,

1:if(Isnull(Sales) 0, Sales)     AS Sales  or

2: if len(trim(Sales)) =0, 0,Sales ) as Sales, or

3. Alt(Sales.0) as Sales    

...

Resident Table;

You can select only one from 3 Options.

or you can try this way of proceeding :

Script-Area:

NullASValue *;

Set NullValue = '-';

Noconcatenate

NewData:

Load

     Date

Resident Calendar

Left Join (NewData)

Load

Date,

IF(LEN(Sales)='-', 0, Sales)     AS Sales

...

Resident Table;

i hope that helps

beck

[Frank_Hartmann]

Hi Raju,

try creating a mastercalendar and connect your table to it by a common field. then use this common field as a dimension in your chart/table.

this will populate the 0-values for the missing dates.

See attached qvw.

hope this helps