topic Re: counting from max(logindate) (Set Analysis question) in QlikView

counting from max(logindate) (Set Analysis question)

Tue, 24 Jul 2012 13:09:14 GMT

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?

Re: counting from max(logindate) (Set Analysis question)

Tue, 24 Jul 2012 13:24:17 GMT

Hi Kevin

try this:

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

Re: counting from max(logindate) (Set Analysis question)

MayilVahanan — Tue, 24 Jul 2012 13:26:49 GMT

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

Re: counting from max(logindate) (Set Analysis question)

Tue, 24 Jul 2012 13:39:44 GMT

Hi Pari,

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

Re: counting from max(logindate) (Set Analysis question)

Tue, 24 Jul 2012 13:44:17 GMT

i'm pretty sure both expressions should work correctly

Re: counting from max(logindate) (Set Analysis question)

Tue, 24 Jul 2012 13:54:35 GMT

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!

Re: counting from max(logindate) (Set Analysis question)

Tue, 24 Jul 2012 14:03:25 GMT

send same data sample

i think there is something with date format:)

Re: counting from max(logindate) (Set Analysis question)

Tue, 24 Jul 2012 14:06:06 GMT

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

Re: counting from max(logindate) (Set Analysis question)

MayilVahanan — Tue, 24 Jul 2012 14:06:56 GMT

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: counting from max(logindate) (Set Analysis question)

Tue, 24 Jul 2012 14:54:09 GMT

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..

Re: counting from max(logindate) (Set Analysis question)

Tue, 24 Jul 2012 15:14:50 GMT

send only 1 row of the table:)

you can do it by :

first (10) load....

Re: counting from max(logindate) (Set Analysis question)

MayilVahanan — Tue, 24 Jul 2012 18:32:22 GMT

Hi,

Try like this,

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

Check both fields are in same format

Hope it helps