4 Replies Latest reply: Oct 25, 2017 2:14 AM by Vikas Desai RSS

    Customer with Activity in past 12 months

    Shwu Hua Gan




      I need to count customer that has Activity in the past 12 months based on the period selected and I will need to show it in a table for 12 rolling months.

      Above table indicate when the customer has transactions.


      In Qlik Sense, I have the selection for Fiscal Period.

      So, if I choose Fiscal Period 2017-12, the table will give me 12 months from 2017-01 to 2017-12

      Active customer count should show as follows:

      2017-01 = 1

      Each period from 2017-02 up to 2017-12  = 2


      However, if I choose Fiscal Period 2017-11, it will show

      2016-12 = 1

      2017-01 = 2

      Each period from 2017-02 up to 2017-11 = 3


      The follow set analysis does not work as it will give me what is shown on the table.

      =Count({<FinYrPeriod2={">=$(vPeriod12MonthsBack)<=$(vPeriodSelected)"},TransactionType={"Last Activity"} ,FiscalYear=,FiscalPeriod=>} CustomerNo)



      The following does not work either:

      =Count({<TransactionDate={">=Min(TransactionDate))<=Max(TransactionDate)"},TransactionType={"Last Activity"} ,FiscalYear=,FiscalPeriod=>} CustomerNo)


      For 2017-12 - rolling 12 months

      Min(TransactionDate) = 28/6/2016 and Max(TransactionDate)=30/6/2017


      For 2017-11 - rolling 12 months

      Min(TransactionDate) = 1/5/2016 and Max(TransactionDate)=31/5/2017


      Using expression to find Min(TransactionDate) and Max(TransactionDate) works.  But, it does not seem to work when I put it in Set Analysis as it just return zero.


      Any help is appreciated.