Date difference between two transactions

[#1]

Hi Everyone,

I have sales records table containing following columns

• OrderID
• Customer Name
• Sale Date
• Product Sold
• Sales Rep Name

Order ID is not unique as one order can contain multiple products.

What would be the expression to count following

average interval in days between each purchase by customers!

So as an example, I would like to evaluate which customer is buying more frequently and which customer is buying less frequently?

[Anil_Babu_Samineni]

May be this?

Avg({<[Product Sold] = {"=Count([Customer Name] >= 0 and Count([Customer Name] <= 1))"}>} [Customer Name])

[tresesco]

Could you share a sample data set, may be in excel or in inline load with qvw?

[jonathandienst]

Does each OrderID have only one Sale Date, or can there be several?

If several, which interval do you wish to use (max order to min order date / average order date to average order date etc)?

[arasaraja_cts]

Hi,

PFA, hope it will help.

Customer 1 purchases product at an interval of 10 days, Customer 2 at 3 days and Cutomer 3  makes a purchase once in every 15 day.

Thanks

Raja.

[#6]

Thank you for your solution Arasaraja,

I can see in your load script that you are doing inline load (which in my case will be the actual table load).

but May i know what is the purpose of loading Interval after loading the main table? (I could not see anything special int that load)

Thank you

[#7]

Hi Jonathan,

Each order id only contains 1 date. If it had more then 1 dates such as (order date, shipping date, etc,) then it would be have been easier to count interval between those dates.

But what I want to count is the average gap between each orders by customer names. or product names.

Thank you

[arasaraja_cts]

Hi,

It is not required you can remove lines 36 to 51 forgot to remove, only the inline table will do.

Thanks

Raja

[jonathandienst]

You want an average per customer, or an average overall? Do you want to calculate in the front end or in the load script?