Create a date iterator object

[Former Member]

Fiscal Year	Fiscal Quarter	FQ Start Date	FQ End Date
2012	1	10/1/2011	12/31/2011
2012	2	1/1/2012	3/31/2012
2012	3	4/1/2012	6/30/2012
2012	4	7/1/2012	9/30/2012

I have a report matching the above format showing the first and last day of our fiscal years (start in October here).

I'm wanting to create a table that populates every date within the fiscal quarter. It would look like this:

Fiscal Year	Fiscal Quarter	Date
2012	1	10/1/2011
2012	1	10/2/2011
2012	1	10/3/2011
2012	1	10/4/2011

....

2012	1	12/30/2011
2012	1	12/31/2011
2012	2	1/1/2012
2012	2	1/2/2012

I assume I'd utilize the DateRelative() function and have a reset argument somehow.

Any Idea on how I'd accomplish this?

Thanks!

[Former Member]

What's your data source? If you're over a normal database (rather than BW), then create an extra data provider to just return the list of dates that you want from your calendar table (if you have one - if you don't, you should create one given you don't have a January to December fiscal year). Merge the queries on date and then use the date from the new data provider, qualifying it with the query name, i.e. =[Query].[Date Object] rather than just =[Date Object]

[Former Member]

Hi,

try usnig the TimeDim() function to generate all the dates needed.

[alldates ] = TimeDim([FQ Start Date];Daily)

Regards,

Rogerio