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?
Re: AsOfDate - is there a workaround apart from this?
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