Active Customers in Date Range

[johnso2080]

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

[orital81]

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.

[johnso2080]

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

[orital81]

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

[johnso2080]

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!

[orital81 · Accepted solution]

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

See attached file

[johnso2080]

Brilliant, thank you very much for your help

[orital81]

Your welcome (-:

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

Ori