Thanks John. You've actually helped me out in the past with a month over month delta report using your AsOf technique:
Dimension: AsOfYearMonth
Expression: MonthsBack(0,1)
That worked great. My new dilemma is with line charts that are a mixture of quarters and months. For example, a user can select Q1 2010 through Q1 2011 plus Apr 2011, May 2011, and Jun 2011 from my Range field to display annualized monthly attrition rates. Everything works because my expressions are based on current quarter or month. However, the monthly calculation has since changed, so part of the expression for Apr 2011, May 2011, and Jun 2011 has to look at the previous month's headcount.
After reading through numerous AsOf examples last night, I'm stumped as to how to create a new AsOf field that is a combination of Quarters and Months. I tried placing the AsOf code after my JOIN INTERVALMATCH section, but that didn't work. So after creating my quarterly and monthly periods, I'm attempting to do something like this:
LEFT JOIN (Range) IntervalMatch (feeddate) LOAD RangeStart, RangeEnd RESIDENT Range;
AsOf2:
LOAD
Range as AsOfRange
,1 as CurrentMonthFlag
,Range
RESIDENT Range
;
CONCATENATE (AsOf2)
LOAD
Range as AsOfRange
,1 as PreviousMonthFlag
,if(substringcount('Jan.Feb.Mar.Apr.May.Jun.Jul.Aug.Sep.Aug.Sep.Oct.Nov.Dec',Range)>0, text(date(addmonths(feeddate,-1),'MMM YYYY'))) as Range
RESIDENT Range
;
Please let me know if you can help. Thanks again!
