Solved

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

  • 5 December 2017
  • 11 replies
  • 2793 views

Badge
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! 
icon

Best answer by dan_ahrens 7 December 2017, 04:08

View original

11 replies

Userlevel 7
Badge +2
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!
Badge
Thanks Dan!  This has helped me move forward, though not completely.  There's one more piece to this.  I have 10 separate things that I want to track, for example:



Did they download a resource

Did they attend a webinar

Did they watch a training video



These are all separate fields in the MDA table.  For each time period (quarter), each account has 10 true/false values associated.   I don't want to have a new line in MDA for each combination of Quarter and Boolean Value. 



Is there any way to include all of these fields on one chart?  
Badge
Here's an image to accompany my last comment: 



Userlevel 7
Badge +2
I want to make sure I'm understanding the reporting need thoroughly. Help me out with these assumptions and let me know if any are incorrect:


  • The Y-Axis represents product release dates and you are aggregating all customers within that given calendar quarter into two groups using the X-axis boolean field
  • The data that you are tracking using the boolean is a single dimension question such as "did they attend a webinar"
  • You have 10 total Y/N questions that you want to summarize by quarter in this style of report
  • It sounds like you want to combine all of the 10 dimensions into a single "group by" visualization such that instead of each customer only having one possible state (1,0) or (0,1), they will sum the total of the 10 dimensions into an array where some possible states per customer could be (9,1) or (5,5) or (2,8) etc
Is this correct?



One follow up question - would it not be valuable to show did data broken out where there is a report per dimension so that you can see the impact of the various activities (webinar, downloaded resource, training video, etc)?



Another alternative would be to show all 10 dimensions on a single report, but you could only report on a single release date at a time. 



Let me know if this helps or alternatively I'd be happy to jump on a call to work though the details in person. Shoot me a note at dahrens at Gainsight dot com if you'd like to discuss further.
Userlevel 7
Badge +2
One other point I'll add is that it often helps to start with understanding how your data will look in a table (as all visualizations are representations of data that starts in a table). That can help you determine what your columns and rows would be and how you would group them. 
Badge
Hi Dan - answers to your assumptions below:




  • The Y-Axis represents product release dates and you are aggregating all customers within that given calendar quarter into two groups using the X-axis boolean field
I set up the chart in the above example using Release Date (just to get a better feel for the group by and how that would affect the chart) but really I just care about the latest release date:








  • The data that you are tracking using the boolean is a single dimension question such as "did they attend a webinar"
Yes, and I also have a corresponding number field which tells me how many webinars they attended, videos watched, etc in the time period.


  • You have 10 total Y/N questions that you want to summarize by quarter in this style of report
Yes


  • It sounds like you want to combine all of the 10 dimensions into a single "group by" visualization such that instead of each customer only having one possible state (1,0) or (0,1), they will sum the total of the 10 dimensions into an array where some possible states per customer could be (9,1) or (5,5) or (2,8) etc
Yes, some accounts will have done some of the 10 activities, while others may have done a different combination.  But I want to show "Out of all of our 2500 accounts, how many did X, how many did Y, how many did Z" - not really caring to know the names of the accounts.  



In response to your last comment:



Totally understand.  I think in order to get what I want, I'd need a table that looks like this:





However, what I currently have is this:







I just need to decide if I want to transform my table into the one above, right?
Badge +3
FWIW it is fairly common to maintain data in both pivoted and non-pivoted forms. 
Userlevel 7
Badge +2
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. 
:)
Badge
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!
Userlevel 7
Badge +2
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. 
Badge
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.



Reply