new customers report with period selection

[#1]

I have a table of purchases :

YYYY MM Customer NetSales Product

YYYY MM Customer NetSales Product

YYYY MM Customer NetSales Product

I would like to know the new customers (that never purchase or did not purchase since 12 months).

what I did :

When I load the data into QV, I add 2 columns : max(order_date) and max(order_date,2) so that I can compare when the customer did his last order.

This solution give me the new customers from Today but I would like to be able to select the period. How many customers I got in Nov 09, Dec 09,Jan 10 (it is a date selection)

Thanks for your help .

[#2]

You can use the selection to define your range in a Set Analysis expression. Something like:

Count{<order_date = {'>$(=AddYears(Max(order_date), -1))
<$(=Max(order_date))'}>} sales)

When you make a selection, the Max(order_date) in the Set Analysis expression will be your selected date. Basically, you would be counting any sales that occur between 1 year before the selected date and the selected date. You didn't give the name of your sales field, so I assumed sales.

Also, this expression assumes order_date is a date. It looks like it may be a string. If it is a string you could convert it to a number (YYYYMM) and then subtract 100000. It all depends on what your data looks like.

[#3]

Hi,

If I use your expression, it will give me the number of sales, correct ?.

What I want to get is number of new customers: either they never ordered or they ordered 1 year from selected date.

Can you help me ?

thanks