This should give you a calendar table with Dates, Months, Years and Quarters from Jan 1st 2012 until Today (Set by variables):
Let vStartDate=num(date#('2012-01-01', 'YYYY-MM-DD'));
Date(DateNum) as Date,
Year(DateNum) as Year,
Month(DateNum) as Month,
Ceil(Month(DateNum)/3) as Quarter,
Year(DateNum) & Week(DateNum) as YearWeek,
$(vStartDate)+RecNo() as DateNum
Then for expanding Months into Dates etc., look at the Pivot table chart object
There is also the tree view functionality in the listbox. See attached example.
For counting the dates, take a look at the Count() function. Depening on what you want to achieve, you may or may not want to use the distinct keyword. I have put an example of this in the attached example too.
DateHierarchy.qvw 149.8 K