Rolling 12 Month Question

[tsglenn22]

This is a question regarding the "As of" table post found here:

https://community.qlik.com/blogs/qlikviewdesignblog/2015/11/02/the-as-of-table

It appears I am unable to attach a file to a comment so I would like to use this post as a means to provide sample data to accompany my question to Henric Cronström. hic

To restate, my question from that Blog Post was about set analysis and was:

"My indicator only has a 1 in the month in which it originated, so using the OPEN_INDICATOR as its own set would only return one month and not return the past 12 months. My goal is to show the balance of new originations (originated in the last 12 months) on a trend line chart with As of Date as my dimension.  As an example, at the as of date of 9/30/2015 I want to sum up balances originated between 10/1/2014 and 9/30/2015, but at 8/31/2015 I want to sum up the balances originated between 9/1/2014 and 8/31/2015.  I’ll send you a message with an example app to help illustrate what I’m describing.  The balance I’m looking for in my example app at 9/30/2015 should be $5,796.79."

This is ideally intended for Henric to see as a supplement to my previous question, but if anyone else could help it would be gladly welcome.

Thank you!

[hic]

It is probably possible to do this with Set Analysis, but I think it would be a very complex expression. Instead, I would prepare the data in the script. One way could be the following:

tmpMonthlyData:
LOAD
BUSINESS_DATE,
AR_ID,
ADJ_AVG_BAL,
OPEN_INDICATOR
FROM [x.xlsx] (ooxml, embedded labels, table is Sheet1);

MonthlyData:
LOAD
Round((BUSINESS_DATE - ORIGIN_DATE)*12/365.2425) as Age,
*;
LOAD
BUSINESS_DATE,
AR_ID,
ADJ_AVG_BAL,
OPEN_INDICATOR,
If( OPEN_INDICATOR, BUSINESS_DATE, If(Peek(AR_ID)=AR_ID,Peek(ORIGIN_DATE))) as ORIGIN_DATE
Resident tmpMonthlyData
Order By AR_ID, BUSINESS_DATE;

Drop Table tmpMonthlyData;

Here you create the Age (in months) for each record, and then you can use the Age in a Set Analysis expression.

HIC