Average number of days between transactions

[behdadian]

Dear all

Hi

I'm new to Qlikview and I want to calculate the average number of days between each purchase for customers.

LOAD * INLINE [

    CustomerID, TranscastionTime, Sales

    101, 2015-09-11, 32

    102, 2015-09-12, 11

    101, 2015-09-18, 20

    101, 2015-10-13, 25

    102, 2015-08-18, 14

    103, 2010-07-09, 22

    103, 2014-09-03, 16

    104, 2015-03-08, 19

];

For example for CustomerID=102 it would return 33.

[MarcoWedel · Accepted solution]

Hi,

one solution could be:

table1:
 LOAD * INLINE [
    CustomerID, TransactionDate, Sales
    101, 2015-09-11, 32
    102, 2015-09-12, 11
    101, 2015-09-18, 20
    101, 2015-10-13, 25
    102, 2015-08-18, 14
    103, 2010-07-09, 22
    103, 2014-09-03, 16
    104, 2015-03-08, 19
];

Left Join (table1)
LOAD CustomerID,
     TransactionDate,
     If(CustomerID=Previous(CustomerID),TransactionDate-Previous(TransactionDate)) as DaysLastTransact
Resident table1
Order By CustomerID, TransactionDate;

hope this helps

regards

Marco

[behdadian]

Thanks Marco. Your solution works for my example. but what I forgot to mention is that: there are multiple records in each transaction for every product.

LOAD * INLINE [

    CustomerID, Product, TransactionTime, Sales

    101, A,  2015-09-11, 32

    102, A,  2015-09-12, 11

    101, A,  2015-09-18, 20

    101, A,  2015-10-13, 25

    101, B,  2015-10-13, 22

    102, A,  2015-08-18, 14

    103, A,  2010-07-09, 22

    103, A,  2014-09-03, 16

    103, C,  2014-09-03, 43

    104, A,  2015-03-08, 19

];

I tried creating another table and aggregate the data there, but it's not really efficient, because it creates another copy of sales table.

[maxgro]

I think MarcoWedel‌‌ solution still works

table1:

LOAD *, rowno() as Id INLINE [

    CustomerID, Product, TransactionDate, Sales

    101, A,  2015-09-11, 32

    102, A,  2015-09-12, 11

    101, A,  2015-09-18, 20

    101, A,  2015-10-13, 25

    101, B,  2015-10-13, 22

    102, A,  2015-08-18, 14

    103, A,  2010-07-09, 22

    103, A,  2014-09-03, 16

    103, C,  2014-09-03, 43

    104, A,  2015-03-08, 19

];

Left Join (table1)

LOAD

  Id,

    If(CustomerID=Previous(CustomerID),TransactionDate-Previous(TransactionDate)) as DaysLastTransact

Resident table1

Order By CustomerID, TransactionDate, Id;

[sunny_talwar]

Making changes to Marco's script:

table1:

LOAD * INLINE [

    CustomerID, Product, TransactionDate, Sales

    101, A,  2015-09-11, 32

    102, A,  2015-09-12, 11

    101, A,  2015-09-18, 20

    101, A,  2015-10-13, 25

    101, B,  2015-10-13, 22

    102, A,  2015-08-18, 14

    103, A,  2010-07-09, 22

    103, A,  2014-09-03, 16

    103, C,  2014-09-03, 43

    104, A,  2015-03-08, 19

];

Left Join (table1)

LOAD CustomerID,

    TransactionDate,

    Product,

    If(CustomerID=Previous(CustomerID) and Product = Previous(Product),TransactionDate-Previous(TransactionDate)) as DaysLastTransact

Resident table1

Order By CustomerID, Product, TransactionDate;

[behdadian]

I used your method for my basic calculations and it worked perfectly. but now that I'm trying to use "average number of days between transaction" in my set analysis, it's getting more and more complicated.

Is there a way to just add the average number of days for each CustomerID in load script?!  for example instead of loading multiple values for a CustomerID, just loading one value for each