Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
provie98
Contributor II
Contributor II

Count All, No Distinct, Set Analysis

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? 

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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?)?

View solution in original post

7 Replies
MK_QSL
MVP
MVP

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

provie98
Contributor II
Contributor II
Author

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

Anonymous
Not applicable

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


provie98
Contributor II
Contributor II
Author

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

Set Analysis.PNG.png

swuehl
MVP
MVP

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?)?

Anonymous
Not applicable

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

provie98
Contributor II
Contributor II
Author

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.