Skip to main content
Product Release Webinar: Qlik Insider airing December 6! REGISTER TODAY!
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Re: Set Analysis Calculation Help

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;

Range as AsOfRange
,1 as CurrentMonthFlag
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

Please let me know if you can help.  Thanks again!

0 Replies