5 Replies Latest reply: Aug 22, 2012 10:28 AM by Mark Sheraton RSS

    AsOfDate - is there a workaround apart from this?

    Amirali Vastani

      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?
          Mark Sheraton

          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