How to link master calendar dates to your dates in data

[#1]

Hi

My requirement is that i have to indicate if an event had occurred on day. It is like a boolean, either i have data for that day or i do not have data for that day. For this i had to create and use master calendar so that all the dates for a given month are there (irrespective of whether i have the data for that day or not). If I have data for that day I have to mark that data is available (any kind of visualization like bar or line chart is okay) and if i don't have data i could leave it blank by not marking anything.

What I tried:

1. On X-Axis i had dates from my data (not master calendar) and selected Continuous axis which put all the dates (though i don't have data for a day). But the problem is that scroll bar does not appear and x-axis scale is automatically set and i have no control over it.

2. I used master calendar, which puts all the dates on X-axis but i have a problem in linking my data to master calendar dates (both are two different tables and have no link with each other).

Thanks for your support in advance.

[jpenuliar]

You can suppress zero values, dates 8th, 9t and 16th will no longer appear

[jpenuliar]

Another trick is to clone [Fact_Dates] and have the clone linked to your calendar.

then you can use your Original [Fact_Dates] field as Dimension

[#4]

I wanted the dates 8th,9th and 16th to be shown on the dimension axis, but there should be no bar for those dates.

[shanemichelon]

Done:

I didn't have your source data spreadsheet so I fluffed it with an INLINE table.  My missing dates are 5,8, and 9 July 2013.

[shanemichelon · Accepted solution]

I think the solution I have provided is exactly what you want.  If so please mark as correct answer.  If not, please provide feedback.  To give you a little more detail about how the result is achieved, here is the script:

Load * inline [
Facts_Dates,EventOccured
1 July 2013,1
2 July 2013,1
3 July 2013,1
4 July 2013,1
6 July 2013,1
7 July 2013,1
10 July 2013,1
11 July 2013,1
];

The expression used for the bars is:

if(Count(EventOccured)=0,Dual('No Data',Max(total Aggr(Count(EventOccured),Facts_Dates))/2),Count(EventOccured))

The dimension is simply dates taken from a master calendar:

QuartersMap: 

MAPPING LOAD  

rowno() as Month, 

'Q' & Ceil (rowno()/3) as Quarter 

AUTOGENERATE (12); 

Temp: 

Load 

               min([Facts_Dates]) as minDate, 

               max([Facts_Dates]) as maxDate 

Resident Facts; 

Let varMinDate = Num(Peek('minDate', 0, 'Temp')); 

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp')); 

DROP Table Temp; 

TempCalendar: 

LOAD 

               $(varMinDate) + Iterno()-1 As Num, 

               Date($(varMinDate) + IterNo() - 1) as TempDate 

               AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 

MasterCalendar: 

Load 

               TempDate AS [Facts_Dates], 

               week(TempDate) As Week, 

               Year(TempDate) As Year, 

               Month(TempDate) As Month

Resident TempCalendar 

Order By TempDate ASC; 

Drop Table TempCalendar;