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.
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
Christian, I already have a master calendar. Not sure what you mean by sum(nothing)=0.
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 :
And check the "Invisible" box in the Expressions tab.
Hope it helps,
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.
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.
You need a reference table with all days, and then join with your desire table, like this:
REF_DATE as DATE
[Header 1] as DATE,
[Header 2] as hValues
change the missing values to "0", and then Supress Zero-Values and try this options:
- ALT( hValues, 0 ) as new_hValues or..
- if(hValues='-',0,hValues) as new_hValues