topic Re: Count All, No Distinct, Set Analysis in QlikView

Count All, No Distinct, Set Analysis

provie98 — Wed, 16 Apr 2014 19:07:36 GMT

Hey all,

I need help with a formula. I don't know if I'm just not understanding set analyis but I'm trying to get a Total Count (not distinct) of Activity Dates by Year. Here's my formula:

Count({$<Year={$(=Only(Year))}>}ACTIVITY_ADD_DATE)

I can't get it count all the dates. It's only counting the distinct values of activity add date. Any idea what I can do?

Re: Count All, No Distinct, Set Analysis

MK_QSL — Wed, 16 Apr 2014 19:20:29 GMT

Count({$<Year={'$(=Max(Year))'}>}ACTIVITY_ADD_DATE)

Re: Count All, No Distinct, Set Analysis

provie98 — Wed, 16 Apr 2014 19:24:56 GMT

That didn't work either but I appreciate the suggestion. It's still only bringing back the distinct count of the dates.

Re: Count All, No Distinct, Set Analysis

Anonymous — Wed, 16 Apr 2014 19:26:50 GMT

You better upload an example of your application, with a little explanation what's wrong.

Re: Count All, No Distinct, Set Analysis

provie98 — Wed, 16 Apr 2014 19:36:22 GMT

Basically, I'm trying to count all of the activity dates below by user for each year. Click on a year, it will give you the current year as well as the previous year; thus that's why the "Only" is part of the Set Analysis Formula. The information below for Agresta shows the count of activity dates as 8 but if you look at the information below it shows more than 8 rows of code. I want to count all of the activity dates not just the distinct dates. For example, if I have 10 rows with 01/19/2014 and 5 rows with 01/31/2014 and 6 rows with 02/28/2014, I want the total count to be 21 but my formula is bringing me back only a count of 3 (3 distinct dates).

Re: Count All, No Distinct, Set Analysis

swuehl — Wed, 16 Apr 2014 19:57:10 GMT

How does your data model look like?

Are you sure you are not seeing a join of unlinked tables in your second image (a table box, right?)?

Re: Count All, No Distinct, Set Analysis

Anonymous — Wed, 16 Apr 2014 20:01:37 GMT

That means that in fact you want to count the number of rows for the year from the table below, and it is larger than the number of dates. The number of rows is defined by the number of unique combinations of the dimension values.

It would be easier to help you if you upload the app, screenshots are helpful but not enough...

Re: Count All, No Distinct, Set Analysis

provie98 — Wed, 16 Apr 2014 20:02:01 GMT

I appreciate all of your help. I'm gonna close this case. I ended up using another date instead of the activity date field and it's bringing back what I wanted. I appreciate all of your insight.