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;
