Count Based on Unique Identifier

VRIndy
VRIndy Member
in Magic ETL

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:

 

IDPersonResponse
1JohnYes
1JohnYes
2PaulNo
3Paul Yes
4PaulNo
4PaulNo
Desired OutputYESNO
John1 
Paul12

 

Any help would be greatly appreciated.

Thank you.

 

 

 

 

Best Answer

  • Valiant
    Valiant Coach
    Answer ✓

    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:image.png

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    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.

     

     

Answers

  • Valiant
    Valiant Coach
    Answer ✓

    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:image.png

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    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
    VRIndy Member

    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?

     

  • Valiant
    Valiant Coach

    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
    VRIndy Member

    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
    cwagner Member

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

Categories