Count with date limitation

[#1]

Hi all,

I am a new QV user and sttempting to do the following - count the number of distinct clients within 1 year.

I got the first part done by using this expression:

count(distinct CUSTOMER_ID)

How do I now also add a date range to the above expression? For example between Jan 1 2012 to Jan 1 2013?

Thanks!

[swuehl]

=count(distinct if(Date >= makedate(2012) and Date <= makedate(2013),CUSTOMER_ID))

or using set analysis:

=count({<Date = {">=$(=makedate(2012))<=$(=makedate(2013))"}>} distinct CUSTOMER_ID)

[#3 · Accepted solution]

Thank you for your quick reply. A couple of questions:

- is "makedate" a variable that is created by this code?

- if I understand correctly, "Date" in your code above is using system date? What if I want to change that to another date field in my data? for example purchase_date

[swuehl]

Date in my example is your Date field, you can replace it with your field purchase_date:

=count(distinct if(purchase_date>= makedate(2012) and purchase_date <= makedate(2013),CUSTOMER_ID))

makedate() is a QV function that creates a date value on the fly, using the function arguments (check the Help for more on makedate() function). In the second example, I use a dollar sign expansion with an expression (also check the Help for that topic) to replace this with the date value (like a macro expansion, the date value is evaluated and replaced within the expression string before its evaluated).

[#5]

Thanks a lot for your explanation and help! I will experiment further now!