Filling in data gaps with zero values

[#1]

Hi,

Lets say I have this data for a month:

Header 1	Header 2
01/04/11	6
10/04/11	2
11/04/11	3
15/04/11	5
21/04/11	1

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?

[Anonymous]

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]

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.

[#4]

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]

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]

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

Jonathan

[#7]

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]

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.

[#9]

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]

That should work...

Have a look at this small sample.

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