Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Hi Kevin
try this:
=count({$<logindate={">=$(=date(max(logindate)-365))"}>} logindate)
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
Hi Pari,
Tried that, no progress; going to try Mayil's suggestion
i'm pretty sure both expressions should work correctly
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!
send same data sample
i think there is something with date format:)
=Count({<logindate = {">=$(=AddYears(Date(Max(logindate)),-1))<=$(=Date(Max(logindate)))"}>}logindate)
Hi,
=Count({<logindate = {“>24/07/2011"}>}logindate) check wheather there's data ??If so..the above expression also work..
Can you post a sample?
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..