Chart Showing Yes/No (or True/False) Count For Multiple Fields

  • 1
  • Question
  • Updated 4 months ago
  • Answered
I have gathered information from various sources and compiled all values into an MDA table.  For example, how many times and account did X in a certain time period.  This table also contains a boolean field corresponding to each value field.  If the value is greater than 0, the boolean field is True, otherwise, False. 

I want to use this information to create a table that shows a count of all accounts and how many are either True or False for these fields.  An example mock up looks like this:



Each "Resource", as I'm calling them here, is a boolean field in the MDA table.  In this example, we have 2500 accounts and they all have either a True or False value for each field.  I can't figure out how to get a report that looks like this - is it possible?

Thanks for any help or suggestions! 
Photo of Kate Green

Kate Green, Champion

  • 3,280 Points 3k badge 2x thumb

Posted 5 months ago

  • 1
Photo of Dan Ahrens

Dan Ahrens, Gainster (employee)

  • 19,738 Points 10k badge 2x thumb
Hi Kate, this is definitely doable with Gainsight Reporting. I've included a sample below of a similar report that I just mocked up using Timeline as a data source.



Please note the following:
  • You'll need two aggregations in the By: section. The first will be the left hand column data (in this case User Name) and the second will be your boolean (we have a Y/N field denoting exec presence on the event). 
  • Your Show: field will be the data that you want to "count" - in my example it's the number of activities but in your example it would be the Accounts
  • Select Stacked Bar as the visualization and to get full width percentages, select chart options and Show Percentage.
You should be all set!
Photo of Karl Rumelhart

Karl Rumelhart, Official Rep

  • 8,498 Points 5k badge 2x thumb
FWIW it is fairly common to maintain data in both pivoted and non-pivoted forms. 
Photo of Dan Ahrens

Dan Ahrens, Gainster (employee)

  • 19,738 Points 10k badge 2x thumb
Hi Kate, would something like this work where you have a row of data for each activity type & date combination?



If so, here's how you'd do this:
  • Create a new MDA table to reflect the data above. 
  • Use a formula field for the third column and use the text concatenation formula (concat) to combine the date and activity into a single field - see this link.
  • The concatenation is required as you can only have a single field in the "By" area when reporting with an intent to use a stacked bar as the final visualization
  • Use a bionic rules transform task to sum (by counting with a filter) for Sum of Yes and Sum of No fields - see this link
  • Use a load to MDA setup action to write this aggregated data to your new MDA table
  • In the report, use the new MDA table as the source and add the concatenated date and activity field you created to the "By" area and the two new sum fields to the show me. This should give you a table of data that would allow you to create a stacked bar chart. 
:)
Photo of Kate Green

Kate Green, Champion

  • 3,280 Points 3k badge 2x thumb
I don't even need the combination because I want to only look at the most recent release.  Here's what I came up with:



Thanks for all your help Dan and Karl!
Photo of Dan Ahrens

Dan Ahrens, Gainster (employee)

  • 19,738 Points 10k badge 2x thumb
Awesome!

Do you mind showing the report screen that has the Show Me and By areas? I think this would be helpful for any other community members who might have a similar use case. Seeing how your report was structured can give them ideas on how to build their own. 
Photo of Kate Green

Kate Green, Champion

  • 3,280 Points 3k badge 2x thumb
Here you go!  I have Count of Account Name in the aggregated Show me field and then Activity and Participated (yes/no) as the Group By fields.  I used a filter to show me last 90 days, so this will update with each release.