topic Re: Active Customers in Date Range in QlikView

Active Customers in Date Range

johnso2080 — Tue, 30 Apr 2013 09:07:33 GMT

Hello,

I have table of orders and fields 'order date','order reference','customer id'

I need an expression that gives me how many distinct customers (customer id's) have placed an order each day (order date), but have also placed at least one order in the past x number of days...

Can anyone help me out?

Thank you!

Darren

Re: Active Customers in Date Range

orital81 — Tue, 30 Apr 2013 09:17:35 GMT

Hi Darrn

Do you want the x number of days to be dynamic?

Anyway, If you could attach your qvw file it will be easier to assist you.

Re: Active Customers in Date Range

johnso2080 — Tue, 30 Apr 2013 09:34:13 GMT

Hi, sorry I can't add the qvw. Would the expression be different if I wanted it dynamic? Couldn't I just replace the constant with a variable? Do you know what the expression would be if I wanted it as, say, 365 days...?

Thanks

Re: Active Customers in Date Range

orital81 — Tue, 30 Apr 2013 10:28:32 GMT

My suggestion is to add a flag to each customer identifying that he has placed an order

in the last x days. This will be in the script level.

Lets call it: CustomerPlacedOrderFlag

Then add a simple expression:

Count({<CustomerPlacedOrderFlag = {{'1'}}>} Distinct Customer)

Hope that helped

Ori

Re: Active Customers in Date Range

johnso2080 — Tue, 30 Apr 2013 12:45:18 GMT

Thanks Ori, I'm not entirely sure how I would build that in to the script? Is there no way of making it dynamic if I needed to?

I've attached a very brief example and a description of what I want to achieve

Hope it makes sense...

Thank you!

Re: Active Customers in Date Range

orital81 — Tue, 30 Apr 2013 13:35:49 GMT

I can make it only in the script, Unfortunately it will not be dynamic )-:

See attached file

Re: Active Customers in Date Range

johnso2080 — Tue, 30 Apr 2013 13:46:45 GMT

Brilliant, thank you very much for your help

Re: Active Customers in Date Range

orital81 — Tue, 30 Apr 2013 13:55:16 GMT

Your welcome (-:

Just mark as "Correct Answer" so other people could enjoy from this answer.

Ori