14 Replies Latest reply on Dec 5, 2018 7:59 AM by Jessica Singer

# Count unique IDs IF contents of other columns >X

I need to create a calculation where I sum all of the unique member IDs that meet particular criteria contained in other cells. Ultimately, I'd like to know how many Member IDs Attended >1 day AND >=2 Locations. In English, I'd like to know how many participants played at more than one location, more than one time. I'm interested in knowing what percentage of our players play at multiple locations--but a lot of players will play ONE DAY at another location for a tournament, etc. I want to exclude these.

I know that it's just a nested calc, but I can't figure it out.

My source file is full of sensitive info, and the file itself is huge. I've attached a screen shot that should help with visualization. Let me know if you need more. The current AGG in the marks card is Count of Attendance Days, which is Count([Attendance]), and Attendance is just a list of individual dates that participants signed in to the location.

EDIT: I have attached a sample Excel file, with names redacted.

Message was edited by: Jessica Singer

Message was edited by: Jessica Singer

• ###### 1. Re: Count unique IDs IF contents of other columns >X

Can you attach some relevant simple data in excel. Only 10 to 15 lines and your requirement.

• ###### 2. Re: Count unique IDs IF contents of other columns >X

I have attached. Any help is appreciated.

• ###### 3. Re: Count unique IDs IF contents of other columns >X

Hi Jessica,

Looks like the example data has only one location. A bit difficult to assess what you'd like to do with just one location. Can you upload a different set of data? Thx, Don

• ###### 5. Re: Count unique IDs IF contents of other columns >X

Thanks Jessica...  What should be used for Member ID?  In the Excel data, there are 4 different ID fields as Dimensions and 1 Member Num as Measure (which has no values other than 0); lastly, what field would be indicative of a tournament so that it would be excluded from the count?  Don

• ###### 6. Re: Count unique IDs IF contents of other columns >X
 member_id

is the correct field for unique users.

I have created a calculated field that counts days of attendance, Count of Attendance Days = COUNTD(attend_date).

I want to count all members whose "Count of Attendance Days" is > 2 AND attended > 2 in multiple locations.

• ###### 7. Re: Count unique IDs IF contents of other columns >X

Hi Jessica,

See if the below helps? I also attached a 2018.3 workbook. Thx! Don

• ###### 8. Re: Count unique IDs IF contents of other columns >X

Thank you, but this isn't quite what I am looking for.

I want to count member IDs, not locations. I want to know over all locations, how many members played at more than one location more than once. It looks like here, Count of Location is counting how many times a member played at a site, instead of the desired other way around.

I want to know how many participants, in total, played >2 times at > 1 locations. I'd like to know how many participants at each Location played > 2 times at > 1 Location (drill down).

• ###### 9. Re: Count unique IDs IF contents of other columns >X

Hi Jessica,

Regardless of whether Location or Member ID is used, the results will be the same.

Please see below screenshot and attached which modifies count of Location to then use count of Member ID 1.  Thx, Don

• ###### 10. Re: Count unique IDs IF contents of other columns >X

I must not be understanding what's being counted here.

The reported count is 4. 4 of what?

• ###### 11. Re: Count unique IDs IF contents of other columns >X

Hi Jessica,

Member ID 176775 played 4 times at Newton High. They played greater than (> 2) 2 times at that location. They have not played at any other location > 2.  Here is the underlying data showing on 4 different dates/occasions they played at Newton.

• ###### 12. Re: Count unique IDs IF contents of other columns >X

Ok, that's what I thought.

This is not the information I am trying to calculate.

I want to count the number of members who played at more than one location AND more than one time. NOT how many times they played anywhere > 2.

For example:

This member played 3 times at JHS 167 and 20 times at Newtown HS. I want to count this member.

Member 176677 played 1 time at JHS 167 and 14 times at Newtown HS. I DO NOT want to count this member.

Member 176882 played 18 times at Newtown HS. I DO NOT want to count this member.

• ###### 13. Re: Count unique IDs IF contents of other columns >X

Hi Jessica

That's a much clearer example and explanation; thank you. In using your example member ID's provided, here's what I have in the data available to me that you provided prior.  The only filter applied here are the Member ID's you've noted in your example.

That being said, the following are the ONLY examples in your provided data where a player played more than one location.  However, based on your most recent explanation, all of them would automatically be excluded. So, essentially, anyone tackling this problem would automatically fail due to the amount and type of data being provided.

Perhaps better data will be provided for someone else to tackle this problem. Thx Don

• ###### 14. Re: Count unique IDs IF contents of other columns >X

Thanks Don. I will attach the entire redacted file to this and see if others are ale to help me.