Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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

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

See attached example.


talk is cheap, supply exceeds demand
Not applicable

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

I using personal edition.

Can you show the scripts or solution here.

Thank in advance.

Not applicable

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

Can put the solution or scripts here?

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

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

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?

Not applicable

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

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

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

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
Community Browser