Year on Year Repeat Count of Customer

[qliksenseadminthomascook]

Hi all,

I am working on a logic where we have to show the repeat and new customer. If the customer was repetitively coming in current year or previous years then that customer is Repeat Customer. If the customer came only once in the year and there is no precedence of the customer then it is new customer. And I have to show the repeat customer in 4 ways. Current Year Repeat (Customer came in current year and is repititive), Previous Year Repeat (Customer Came in previous year and current year. whether it be once or more than once in each year), Previous to Previous Year Repeat (Customer Came in 2 years bach and in current year. whether it be once or more than once in each year) and PtoP to current Year Repeat(Customer Came in all 3 years. Like Previous years and current year. whether it be once or more than once in each year). Attached a sample excel file for reference.

Thanks in advance

[rubenmarin]

Hi, this would be easy if you want a current state chart, where you can precalculte this on script:

1. create auxiliary tables to identify customers in previous years, like:

chkPtoP:
LOAD Distinct Customer as chkPtoP
Resident DataTable 
Where Year=Year(Today())-2;

chkP:
LOAD Distinct Customer as chkP
Resident DataTable 
Where Year=Year(Today())-1;

2. Load customers to do the checks:

tmpCustomerType:
LOAD 
  Customer,
  If(Exists('chkPtoP',Customer),1,0) as isPtoP,
  If(Exists('chkP',Customer),1,0) as isP,
  Count(Customer) as NumCY
Resident DataTable 
Where Year=Year(Today())
Group By Customer
;

DROP Table chkPtoP;
DROP Table chkP;

3.Calculate conditions

CustomerType:
Noconcatenate LOAD
  Customer,
  If(isPtoP
    ,If(isP
      ,'PtoP to CY RPT'
      ,'PtoP RPT')
    ,If(isP
      ,'PY RPT'
      ,If(NumCY>1
        ,'CY RPT'
        ,'CY New'
))) as CustomerType
Resident tmpCustomerType;

DROP Table tmpCustomerType;

Not tested, maybe there is a typo, but I think you can get the idea from this.

[qliksenseadminthomascook]

Hi Rebenmarin,

Thank you for your inputs. However I need to show the data based on the selections and this will give me the data only it is static. Can you please help me for the selections as well. Like if the end user selects the year, branch or any other filter given, it should give the data accordingly which I believe we can do it with expressions and set analysis only.

Thanks 

[marcus_sommer]

I think I would use interrecord-functions to create appropriate counter/offset/flag-information within the data-model. This means simplified something like:

load *, if(CustomerID = previous(CustomerID), peek('Nr') + 1, 1) as Nr
resident X order by CustomerID, Date desc;

which creates a running counter per customer. But with such an approach you could also calculate the date-offset between the current record and the previous one and/or against today() and/or against the daynumberofyear(). On top may come a clustering of the offsets in months/years and some if-checks on them.

[rubenmarin]

Hi, in example from a data like this:

Using this expression:

If(Max(Year)=Max(TOTAL {1<Year={"<=$(=Max(Year))"}>} Year) and Max({<Year={"<=$(=Max(Year))"}>} Year,2)=Max(TOTAL {1<Year={"<=$(=Max(Year))"}>} Year,2) and Max({<Year={"<=$(=Max(Year))"}>} Year,3)=Max(TOTAL {1<Year={"<=$(=Max(Year))"}>} Year,3)
  ,'PtoP to CY RPT'
  ,If(Max(Year)=Max(TOTAL {1<Year={"<=$(=Max(Year))"}>} Year)  and Max({<Year={"<=$(=Max(Year))"}>}Year,2)=Max(TOTAL {1<Year={"<=$(=Max(Year))"}>} Year,3)
    ,'PtoP RPT'
    ,If(Max(Year)=Max(TOTAL {1<Year={"<=$(=Max(Year))"}>} Year)  and Max({<Year={"<=$(=Max(Year))"}>}Year,2)=Max(TOTAL {1<Year={"<=$(=Max(Year))"}>} Year,2)
      ,'PY RPT'
      ,If(Sum(Data)>1
        ,'CY RPT'
        ,If(Sum(Data)=1,'CY New'
)))))

It will work based on year selections, and it should work also with branch selections

And selecting 2024: