topic Re: Count Error in QlikView

Count Error

hkg_qlik — Mon, 18 Jun 2012 13:12:02 GMT

Hi,

The business rule is that If i login multiple times on the same date the count of PersonID associated with me should be one for that day.

Currently I am have issue with that when I am trying to show the count of PersonID in terms of Month - year.

If i am using date field and counting the PersonID its giving me a correct count as i can do distinct and count the no. of rows.

Please look at the attached .qvw and the excel data.

Thanks,

Re: Count Error

CELAMBARASAN — Mon, 18 Jun 2012 13:20:41 GMT

Hi,

Use expression as

Count(DISTINCT PersonID&Date)

Celambarasan

Re: Count Error

swuehl — Mon, 18 Jun 2012 13:25:14 GMT

Or try something like

=count(aggr(PersonID,Date))

Re: Count Error

hkg_qlik — Mon, 18 Jun 2012 13:49:01 GMT

I tried both the expression but the count which reflects in the table with date(Marked Orange) which is 121 should match when I am showing it by Month and Year.

Thanks,

Re: Count Error

swuehl — Mon, 18 Jun 2012 13:53:30 GMT

Hm,

if I use my expression in your tables using Month or Year as dimension, I get 15 as result, which seems to me the correct answer. What do you get /expect?

Re: Count Error

CELAMBARASAN — Mon, 18 Jun 2012 13:59:57 GMT

Hi,

You should rewrite your expression as

If(Count(DISTINCT PersonID) > 0, Count(DISTINCT PersonID&Date))

because you have calendar months without person id mapped.

Celambarasan

Re: Count Error

swuehl — Mon, 18 Jun 2012 14:06:04 GMT

Try using

=count(aggr(PersonID,Date,PersonID))

=sum(aggr(count(distinct PersonID),Date))

Re: Count Error

hkg_qlik — Mon, 18 Jun 2012 15:05:46 GMT

Thanks for the Expression suggestions.

Swuehl : =sum(aggr(count(distinct PersonID),Date))

This works for me.

Regards,