counting from max(logindate) (Set Analysis question)

[#1]

We want to examine customers who've left our online program. We collect data each time they log in; thus, I know the last day they logged in, which was

simple to find in a table using "max(logindate)".

Now we want to know how many times they logged in during the year PREVIOUS to their last log in. I tried the following using Set Analysis:

=count({$<logindate={">=(max(logindate)-365)"}>} logindate)

To my mind, this instructed the program to count each occurrence of "logindate" greater than or equal to the max(logindate)-365. I checked in a separate table, and "logindate-365" does indeed yield the same day, one day earlier. However, my total is uniformly "0", which I know is incorrect.

Is this a syntax error, or a logic error?

[#2]

Hi Kevin

try this:

=count({$<logindate={">=$(=date(max(logindate)-365))"}>} logindate)

[MayilVahanan]

Hi,

     Try like this,

     =Count({<logindate = {'>=$(=Date(Max(logindate)-365))<=$(=Date(Max(logindate)))'}>}logindate)

     Edit:

          =Count({<logindate = {'>=$(=TimeStamp(Max(logindate)-365))<=$(=TimeStamp(Max(logindate)))'}>}logindate) 

     Check both fields are in same format

     Hope it helps

[#4]

Hi Pari,

Tried that, no progress; going to try Mayil's suggestion

[#5]

i'm pretty sure both expressions should work correctly

[#6]

Mayil,

Thanks, but that didn't work either - still just get "0" for all companies. But just doing a simple count of "logindate" shows numbers that range from 0 to over 2,000, and that latter was for a company whose last login was just a week ago, so they would have to have at least 1 login in their last year.

This is the sort of thing that's CPU intensive but so simple to program in a procedural language like VB; it's trying to figure out to do it in QV that has me baffled!

[#7]

send same data sample

i think there is something with date format:)

[#8]

=Count({<logindate = {">=$(=AddYears(Date(Max(logindate)),-1))<=$(=Date(Max(logindate)))"}>}logindate)

[MayilVahanan]

Hi,

     =Count({<logindate = {“>24/07/2011"}>}logindate) check wheather there's data ??If so..the above expression also work..

Can you post a sample?

[#10]

re: posting samples:

1 - the file is huge (millions of records, takes about 3 minutes just to load into QV)

2 - the data is sensitive - my boss doesn't like it going out of house

3 - I'm not sure how to create a sample app!

However, Mayil, your last note was spot on - just doing that count resulted in ZERO's! I have a listbox "Logindate" open next to my table, and when I select a company that has max(Logindate) = 7/24/2012 (i.e. today), and do a count of logins from 1/1/2011, the listbox "Logindate" has 2860 lines (we use the full date field, down to HH:MM:SS, so we have multiple logins on the same day), but the table shows zero.

Curioser and curiouser..