topic Re: Year on Year Repeat Count of Customer in App Development

Year on Year Repeat Count of Customer

qliksenseadminthomascook — Tue, 30 Sep 2025 11:09:46 GMT

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

Re: Year on Year Repeat Count of Customer

rubenmarin — Wed, 01 Oct 2025 08:19:10 GMT

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.

Re: Year on Year Repeat Count of Customer

qliksenseadminthomascook — Wed, 08 Oct 2025 12:19:55 GMT

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

Re: Year on Year Repeat Count of Customer

marcus_sommer — Wed, 08 Oct 2025 13:26:45 GMT

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.

Re: Year on Year Repeat Count of Customer

rubenmarin — Wed, 08 Oct 2025 19:08:19 GMT

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: