0 Replies Latest reply: Sep 29, 2016 10:39 AM by Dirk Jacobs RSS

    Asof Table - but not including current month

    Dirk Jacobs

      Hi All,

       

      I have a situation where I want to look at the sum(sales) for a rolling previous 3 months BUT NOT including this months' sales.

      Hence when I Qlik on the current quarter I want to see and report the Sales for the previous quarter.

       

      I have looked at Mr. Chronstrom and Harmsen's solutions where you outer join the calendar to itself but that assumes a situation

      where Date of Sale <= Date (Date in second calendar table outer joined to calendar table).

       

      What I did was the following to get the Merchant ID and (Sale)Amount and previous three month amount and average onto the same line in the transaction table which works nicely but I am not sure how to use this to calculate what I need:

       

      SWIPE_AGGR_TMP:

      LOAD

        [Merchant ID] as [%Key Merchant],

        date([Date Posted]) as [Date Posted],

        MonthStart([Date Posted]) as [Date Posted Month],

        MonthStart([Date Posted]) as DATE,

        [Amount],

        Interchange

      from

      [QVD\Transactions.QVD] (qvd)

      where

        date([Date Posted]) <= monthend(today()) and

        date([Date Posted]) >= $(vStartDateTransactions);

       

      //CONCATENATE TO GET THREE MONTHS DATA INTO ONE MONTH

      //conc1

      SWIPE_AGGR:

      NoConcatenate

      LOAD

        [%Key Merchant],

          AddMonths(DATE,1) as DATE,

          DATE as TRAN_DATE,

          Amount as AMOUNT,

          0 as CYCLE

      Resident

        SWIPE_AGGR_TMP;

       

      //conc 2

      Concatenate(SWIPE_AGGR)

      LOAD

        [%Key Merchant],

          AddMonths(DATE,2) as DATE,

          DATE as TRAN_DATE,

          Amount as AMOUNT,

          1 as CYCLE

      Resident

        SWIPE_AGGR_TMP;

       

      //conc 3

      Concatenate(SWIPE_AGGR)

      LOAD

        [%Key Merchant],

          AddMonths(DATE,3) as DATE,

          DATE as TRAN_DATE,

          Amount as AMOUNT,

          2 as CYCLE

      Resident

        SWIPE_AGGR_TMP;

       

      noconcatenate

       

      ////3 MONTHS SWIPE AMOUNT & AVERAGE////////

      SWIPE_AGGR_GROUP:

      LOAD

        [%Key Merchant],

          DATE,

          sum(AMOUNT)/3 as Avg_Amount_3Month,

          sum(AMOUNT) as Amount_3Month

      Resident

        SWIPE_AGGR

      group by

        [%Key Merchant],

          DATE;

      drop table SWIPE_AGGR;

       

       

      //SWIPES: JOIN BACK TO ORIGINAL SWIPE TABLE TO GET THE 3 MONTHS AMOUNT & AVERAGE ONTO RECORD

      left join (SWIPE_AGGR_TMP)

      LOAD

        [%Key Merchant],

        DATE,

        Amount_3Month,

        Avg_Amount_3Month

      RESIDENT

        SWIPE_AGGR_GROUP;

       

      drop table SWIPE_AGGR_GROUP;

       

      What I want to display in the front end is:

       

      1. Analyze the previous three months (quarter) sales when clicking on this quarter on a rolling basis. As an example:

       

      When selecting 2015-Q1 should show sum of Amount for 2014-Q4,

      When selecting 2015-Q2 should show sum of Amount for 2015-Q1,

      When selecting 2015-Q3 should show sum of Amount for 2015-Q2,

      When selecting 2015-Q4 should show sum of Amount for 2015-Q3,

      When selecting 2016-Q1 should show sum of Amount for 2015-Q4,

      etc.


      Another way of stating the problem is the following:

      When selecting 2015-Q1 should show Avg_Amount_3Month for the first month of 2015-Q1 (as per my script above) but display as value for the whole 2015-Q1

      When selecting 2015-Q2 should show Avg_Amount_3Month for the first month of 2015-Q2 (as per my script above) but display as value for the whole 2015-Q2

      When selecting 2015-Q3 should show Avg_Amount_3Month for the first month of 2015-Q3 (as per my script above) but display as value for the whole 2015-Q3

      When selecting 2015-Q4 should show Avg_Amount_3Month for the first month of 2015-Q4 (as per my script above) but display as value for the whole 2015-Q4

      When selecting 2016-Q1 should show Avg_Amount_3Month for the first month of 2015-Q5 (as per my script above) but display as value for the whole 2016-Q1


      Any ideas would be greatly appreciated, for personal contact please mail me at dirk101jacobs@gmail.com.

       

      Kind Regards,

      Dirk Jacobs