Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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)
AApr-14
BApr-14
C May-14
DJun-14
ADec-15
BMay-15
C Jun-15
DMay-15
10 Replies
Gysbert_Wassenaar

See attached example.


talk is cheap, supply exceeds demand
Not applicable
Author

I using personal edition.

Can you show the scripts or solution here.

Thank in advance.

Not applicable
Author

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)


talk is cheap, supply exceeds demand
Not applicable
Author

i am loading the excel table instead of using inline.

In this case, how should the script be written?

Not applicable
Author

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.


talk is cheap, supply exceeds demand
Not applicable
Author

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.


talk is cheap, supply exceeds demand