Finding the same customer who made duplicate transaction in 12 months

[#1]

Hi All,

I like to seek your kind assistance on the question below.

I would like to find who are the distinct customer who made 2 or more transaction within 12 months. I have created a sample table  and the right answer should be highlighting:

1) Customer A (as there are 2 transactions within 8 months)

2) Customer D (as there are 2 transactions within 11 months)

Can someone advise me pls.

Customer	Transaction Date (MM/YYYY)
A	Apr-14
B	Apr-14
C	May-14
D	Jun-14
A	Dec-15
B	May-15
C	Jun-15
D	May-15

[Gysbert_Wassenaar]

See attached example.

[#3]

I using personal edition.

Can you show the scripts or solution here.

Thank in advance.

[#4]

Can put the solution or scripts here?

[Gysbert_Wassenaar]

Script:

Transactions:

LOAD

  Customer,

  Date(Date#([Transaction Date],'MMM-YY'),'MMM-YY') as [Transaction Date],

  1 as Transaction

INLINE [

    Customer, Transaction Date

    A, Apr-14

    B, Apr-14

    C, May-14

    D, Jun-14

    A, Dec-14

    B, May-15

    C, Jun-15

    D, May-15

];

AsOf:

LOAD

  [Transaction Date] as Month,

  Date(Addmonths([Transaction Date],1-IterNo()),'MMM-YY') as [Transaction Date]

Resident

  Transactions

While

  IterNo() <= 12

  ;

Chart expressions:

=count(DISTINCT if(aggr(sum(Transaction),Customer,Month)>1,Customer))

=count({<Customer={"=max(aggr(sum(Transaction),Customer,Month))>1"}>}DISTINCT Customer)

[#6]

i am loading the excel table instead of using inline.

In this case, how should the script be written?

[#7]

i also try your script but it seem not to be correct. any one can advise? i only want to identify how may transaction the customer make within 12 months and identify those with 2 transaction within 2 months.

[Gysbert_Wassenaar]

Replace the INLINE [ .... ]; part with FROM MyExcelFile.xlsx (ooxml, ...etc) ;. Just use the File Wizard to create a load statement for the excel file and adapt it.

[#9]

Used the script but does not show the results which I want

Sent from Outlook Mobile<https://aka.ms/blhgte>

[Gysbert_Wassenaar]

Download and install Qlik Sense Desktop and then open the attached Qlik Sense app. You'll be able to see the script and the expressions used in the app.