Count Based on Unique Identifier

Hello all,

I'm very new to Domo and in particular Beastmode.

In this case, I do not have access to the dataflow behind the chart.

I'm trying to count the number of yes and no responses for each unique ID number to put into a stacked bar chart. My X-Axis is the person and the Y Axis is the total number of unique items stacked by Yes/NO. The same ID will never have a Yes and No. The response will always be the same for each ID, but the ID could be listed many times. Sample data below:

ID	Person	Response
1	John	Yes
1	John	Yes
2	Paul	No
3	Paul	Yes
4	Paul	No
4	Paul	No

Desired Output	YES	NO
John	1
Paul	1	2

Any help would be greatly appreciated.

Thank you.

Find more posts tagged with

Accepted answers

BlueRooster

VRIndy,

To get a unique count based on the ID and Person, I believe the following should work for you. Here is the BeastMode I used in testing:

COUNT(DISTINCT CONCAT(`ID`,`Person`))

And here is a screenshot of the setup/results:

This seems to capture what you're looking for. If not, please let me know how I can help further.

**Please mark "Accept as Solution" if this post solves your problem
**Say "Thanks" by clicking the thumbs up if this post helped you.

All comments

BlueRooster

VRIndy,

To get a unique count based on the ID and Person, I believe the following should work for you. Here is the BeastMode I used in testing:

COUNT(DISTINCT CONCAT(`ID`,`Person`))

And here is a screenshot of the setup/results:

This seems to capture what you're looking for. If not, please let me know how I can help further.

**Please mark "Accept as Solution" if this post solves your problem
**Say "Thanks" by clicking the thumbs up if this post helped you.

VRIndy

Thank you. This worked with one exception. It is showing 1 more response that actual unique id's. Not sure why. I've actually manually counted the data and the unique id count is correct. Any ideas?

BlueRooster

Is it possible that you have either an ID or Person with either a leading or trailing space or some other character?

Is it 1 additional count for every unique ID or for only 1 person?

VRIndy

I've exported the data to excel and used sumproduct to count unique values by id and then concatentated the id with the yes/no and the persons name. Shows 26 in the id column and 27 in the concatentated column. It has to be something in the data. I will keep trying to determine the problem.

The bottom line is the Beastmode formula worked! Thank you I will mark it approved!

cwagner

For some reason my beast mode is not recognizing the concat command. Can you screen shot what your beastmode looks like in the screen?