topic Re: Date difference between two transactions in QlikView

Date difference between two transactions

Mon, 04 Sep 2017 23:22:01 GMT

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?

Re: Date difference between two transactions

Anil_Babu_Samineni — Tue, 05 Sep 2017 01:28:05 GMT

May be this?

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

Re: Date difference between two transactions

tresB — Tue, 05 Sep 2017 04:27:09 GMT

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

Re: Date difference between two transactions

jonathandienst — Tue, 05 Sep 2017 04:58:36 GMT

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

Re: Date difference between two transactions

arasaraja_cts — Tue, 05 Sep 2017 09:41:07 GMT

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.

Re: Date difference between two transactions

Tue, 05 Sep 2017 11:41:15 GMT

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

Re: Date difference between two transactions

Tue, 05 Sep 2017 11:42:37 GMT

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

Re: Date difference between two transactions

arasaraja_cts — Tue, 05 Sep 2017 12:02:26 GMT

Hi,

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

Thanks

Raja

Re: Date difference between two transactions

jonathandienst — Thu, 07 Sep 2017 06:34:40 GMT

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