Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am querying two issues:
1) Is it possible to have a list of dates formatted as :
01/01/2012
02/01/2012
03/01/2012
04/01/2012
05/01/2012
06/01/2012
All the way up to the current date today 23/11/2012, to seperate the dates by Month? so maybe have it formatted to look like
January +
February +
March +
April +
This would then expand to list the dates within January etc.
2) With this list date I would be able to write an if statement which looks at the the day to calculate the total number of instances on that given day? and then do the same for the month field?
Hi Sandston,
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'));
Let vEndDate=Today();
Load
Date(DateNum) as Date,
Year(DateNum) as Year,
Month(DateNum) as Month,
Ceil(Month(DateNum)/3) as Quarter,
Year(DateNum) & Week(DateNum) as YearWeek,
DateNum;
Load
$(vStartDate)+RecNo() as DateNum
AutoGenerate $(vEndDate)-$(vStartDate);
Then for expanding Months into Dates etc., look at the Pivot table chart object
For month you can try
date(Date, 'MMMMM') & ' +' as Month
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.
Hi I am unable to load your example as I am only using the personal free edition.
I was away for last four days so will be picking this back up now.
Hi i added the lines into my edit script but cant see when finally loads. have the error message also when I click the reload button.
I have attached a sample of the data i am using. hope this helps
Any further assistance greatly appreciated.