1 Reply Latest reply: Dec 11, 2015 9:04 AM by Henric Cronström RSS

    Rolling 12 Month Question

    Tanner Glenn

      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!


        • Re: Rolling 12 Month Question
          Henric Cronström

          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