Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

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
jonbrough
Valued Contributor

Re: Filling in data gaps with zero values

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
Valued Contributor

Re: Filling in data gaps with zero values

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

Re: Filling in data gaps with zero values

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
New Contributor III

Re: Filling in data gaps with zero values

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

jonbrough
Valued Contributor

Re: Filling in data gaps with zero values

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

Jonathan

Not applicable

Re: Filling in data gaps with zero values

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

Highlighted
thomduvi83
New Contributor III

Re: Filling in data gaps with zero values

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

Re: Filling in data gaps with zero values

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
New Contributor III

Re: Filling in data gaps with zero values

That should work...

Have a look at this small sample.

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