10 Replies Latest reply: Mar 21, 2016 1:45 PM by Gysbert Wassenaar

# Finding the same customer who made duplicate transaction in 12 months

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
• ###### Re: Finding the same customer who made duplicate transaction in 12 months

See attached example.

• ###### Re: Finding the same customer who made duplicate transaction in 12 months

I using personal edition.

Can you show the scripts or solution here.

• ###### Re: Finding the same customer who made duplicate transaction in 12 months

Script:

Transactions:

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:

[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)

• ###### Re: Finding the same customer who made duplicate transaction in 12 months

i am loading the excel table instead of using inline.

In this case, how should the script be written?

• ###### Re: Finding the same customer who made duplicate transaction in 12 months

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.

• ###### Re: Finding the same customer who made duplicate transaction in 12 months

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

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

• ###### Re: Finding the same customer who made duplicate transaction in 12 months

Well, no. The results of chart expressions will not be visible in the script.

• ###### Re: Finding the same customer who made duplicate transaction in 12 months

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.

• ###### Re: Finding the same customer who made duplicate transaction in 12 months

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.

• ###### Re: Finding the same customer who made duplicate transaction in 12 months

Can put the solution or scripts here?