Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Filling in data gaps with zero values

Hi,

Lets say I have this data for a month:

Header 1Header 2
01/04/116
10/04/112
11/04/113
15/04/115
21/04/111

I need to fill in the gaps between so that all the dates in the month are shown, but with zero against the ones that are zero. This is because when I try to plot this data on a chart I only get 5 values, despite me having created a master calendar so that the other dates exist. I need the chart to show the continous days with zero values against the days that are zero.

Anyone help?

10 Replies
Anonymous
Not applicable
Author

I often have problems with this and am generally relieved when I don't as gaps in a timeseries are a massive no-no. Things you may want to try include:

1. on the date dimension, tick the 'Show all values' checkbox

2. on the presentation sheet, untick the 'Suppress zero values' and 'Suppress missing values'

If that doesn't work, I have in the past resorted to setting up dummy values of 0 in my fact tables for each combination of date and the major dimensions my charts are shown by. I hated myself for doing so and haven't had to use it recently.

Jonathan

christian77
Partner - Specialist
Partner - Specialist

Create a calendar. they are many in these forums...

In your table or chart, go to Properties > Presentation > Uncheck supress cero values and check show ceros.

If your function is SUM, sum(nothing)=0, it'll show cero.

Not applicable
Author

Jon, thanks for your reply.

1. The show all values checkbox kind of worked, but now the dimension axis doesn't adjust when data selections are made.

2. No effect.

How would I create dummy zero values in the fact tables? My data comes from a database so can't edit the source. My data structure is essentially this:

Master Calendar:               Data:

Timestamp                         Timestamp          Value

01/04/11                             03/04/11               6

02/04/11                             11/04/11               2

03/04/11

04/04/11

etc

Christian, I already have a master calendar. Not sure what you mean by sum(nothing)=0.

Thanks

thomduvi83
Partner - Contributor III
Partner - Contributor III

Hi,

The problem is that if you have a selection, the missing dates are not associated, so will never be shown in the axis.

I've solved once this problem by adding another expression like :

Sum({1<Date={">=$(=min(Date))<=$(=max(Date))"}>}1)

And check the "Invisible" box in the Expressions tab.

Hope it helps,

Thomas

Anonymous
Not applicable
Author

Nice tip Thomas. That should save me using this dummy value in the future.

Jonathan

Not applicable
Author

Hi,

Thanks for your reply but adding this expression doesn't seem to do anything.

Is it possible to fill in the missing data in the script?

Thanks

thomduvi83
Partner - Contributor III
Partner - Contributor III

1. Load distinct all the dates

2. JOIN all the dimensions (cartesian product to create every combinaison possible)

3. Create Key to the FACT if needed.

4. JOIN the resulted table to the FACT table.

That will create rows with null values for every non-existing combinaison of the key.

Then the sum on null should work and return 0.

Not applicable
Author

Hi,

Any chance you could be more specific with the scripting requirements as I can't get what you've suggested to work.

I only have one dimension (TIMESTAMP) and a set of values. (Table A)

I have created a master calendar with the field TIMESTAMP which has a row for every single date. (Table B)


I have joined Table A to Table B using an outer join.

My resultant data set still only consists of the dates and values from Table A, not the missing (zero value) dates.

thomduvi83
Partner - Contributor III
Partner - Contributor III

That should work...

Have a look at this small sample.

Don't forget to uncheck "Suppress zero values" in your graph