AsOfDate - is there a workaround apart from this?

[avastani]

I have a situation where I have daily dates from 1976 onwards on a Fiscal basis. I need to be able to get Last 12 months on a monthly level, QTD, YTD, 3YTD, 5YTD.

Trying to use an OUTER JOIN of the MasterCalendar onto itself creates a huge AsOfDate table with all my flags which I don't really need at all because majority of them will have the flags = 0 since a datapoint from AsOfDate = 2012 with years farther away than 5 years is immaterial.

I am looking to truncate the asofDate table or find a workaround to this solution.

The only other thing I can think of is using the MasterCalendar and storing the comparison points as dates in the same record to keep the record count low and then use them in set analysis for Variance computations.

Are there any other workarounds that would be faster than the above?

[RedSky001]

We use a seperate date range table for this.  See the script below, it links back to the master calender with just the Date field.

	DateRanges:
	// Use the "evaluate()" function to execute the input function text and convert it to actual date values.
	LOAD DateRange, date(evaluate(RangeStart)) as RangeStart, date(evaluate(RangeEnd)) as RangeEnd
	;
	/* The INLINE data defines the Range Name, Starting date of the Range, and Ending date of the Range.
	The Start/End values are defined using standard QV date functions.
	Semicolon is used for a field delimeter because some of the functions contain commas.
	*/
	LOAD * INLINE [
	DateRange; RangeStart; RangeEnd
	ALL HISTORY; vMinDate; DayEnd(vToday-1)
	All to This Month; vMinDate; MonthEnd(vToday)
	ALL FORECAST; vToday; DayEnd(vMaxDate)
	Today; vToday; DayEnd(vToday)
	Yesterday; vToday-1; DayEnd(vToday-1)
	This Week; WeekStart(vToday); WeekEnd(vToday)
	Last Week; WeekStart(vToday,-1); WeekEnd(vToday, -1)
	Last 2 Weeks; WeekStart(today(1),-1); WeekEnd(today(1))
	This Month; MonthStart(vToday); MonthEnd(vToday)
	Next Month; MonthStart(vToday,+1); MonthEnd(vToday,+1)
	Last Month; MonthStart(vToday,-1); MonthEnd(vToday,-1)
	Last Two Months; MonthStart(vToday,-1); MonthEnd(vToday)
	This Quarter; QuarterStart(vToday); QuarterEnd(vToday)
	Last Quarter; QuarterStart(vToday,-1); QuarterEnd(vToday,-1)
	This Year; YearStart(vToday); YearEnd(vToday)
	This Year to Date; YearStart(vToday); DayEnd(vToday)
	Last Year to Date; YearStart(vToday,-1); DayEnd(vToday,-365)
	Last Year; YearStart(vToday,-1); YearEnd(vToday,-1)
	Last 2 Years; YearStart(vToday,-2); YearEnd(vToday,-2)
	Last 5 Years; YearStart(vToday,-5); YearEnd(vToday,-5)
	Last 30 days; vToday-30; DayEnd(vToday-1)
	Last 7 days; vToday-7; DayEnd(vToday-1)
	This Month to Date; MonthStart(vToday); DayEnd(vToday)
	Last Month to Date; MonthStart(vToday,-1); DayEnd(AddMonths(vToday,-1))
	] (delimiter is ';')
	;
	/*
	Use IntervalMatch to link the Date field into multiple Range fields.
	Doing a JOIN avoids creating a messy synthetic key.
	*/
	JOIN (DateRanges) IntervalMatch (Date) LOAD RangeStart, RangeEnd RESIDENT DateRanges;
	// Because we did a JOIN, we may drop the the Start/End fields.
	//DROP FIELDS RangeStart, RangeEnd;

Hope this helps.

Mark

[avastani]

Hi Mark,

thanks for the response. I am trying this out. The Date field in the IntervalMatch is from MasterCalendar I presume?

You have JOIN (DateRanges) IntervalMatch (Date)

i.e. meaning

JOIN (MasterCalendar) IntervalMatch MasterCalendarDate

?

[avastani]

The limitation of this methodology is that the date ranges are limited in comparison to vToday. In my case,

1. the user can pick any date and they want to know all the comparison date ranges associated with it.

2. be able to see a historical trend of the data until the date picked

3. know what the data looks like as of the selected date and compare to the comparison points

4. it is all snapshot data i.e. as of a date what is the situation today, tomorrow the data will look different.. just like a stock market. what was the data today compared to yesterday, compared to 2 years ago. Now pick a date in the past and perform same comparisons.

I am going to try to modify the INLINE table. May be that might work

[avastani]

Tried it with replacing vToday by MasterCalendarDate and it doesn't work. Open to other suggestions.

This has got to be something everyone's faced at some point in their QV travels,right?

[RedSky001]

My script won't work in that case as you'd need to reload everytime they changed "vToday"

I would do this with set analysis in the UI (search the forums for rolling month calculations).

Will be intresting to see what others suggest.