Count All, No Distinct, Set Analysis

[provie98]

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? 

[MK_QSL]

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

[provie98]

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

[Anonymous]

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

[provie98]

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

[swuehl · Accepted solution]

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]

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]

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.