Forum Discussion
Visualizing textual data, like YES/NO questions
Howdy folks - first post! Couldn't find the answer to this in the forums, and not sure I can even ask it right, but here goes.
I have a spreadsheet similar to this:
2014 2015
Site 1 YES YES
Site 2 NO YES
Site 3 YES NO
I'd like a visualization / horiz bar chart that would simply show each site, with the two years as sub-groupings under each site, then a solid 100% bar that was either red for NO or green for YES. Or any variation, I'm open, but I'm getting stuck on the YES/NO fields simply getting counted, which doesn't help me (unless I convert the YES's to 0 and NO's to 1 or something).
Any thoughts on how to approach this? Let me know if I can clarify or explain in more detail.
I've dealt with this myself (though in my case it was TRUE/FALSE values). Here's how I solved it:
- Created a new calculated field called "Achieved or Missed" (you should use something appropriate for your data), assign it a numerical value of '1' whenever the original field's value is 'Yes' or a numerical value of '0' whenever the original field's value is 'No'
- Create 1 new measure called '% Achieved' --- SUM('Achieved or Missed') / COUNT('Achieved or Missed')
- Create 1 new measure called '% Missed' --- (COUNT('Achieved or Missed') - SUM('Achieved or Missed')) / COUNT('Achieved or Missed')
- Use a Stacked Bar (or Column) chart and plot the 2 measures.
3 Replies
I've dealt with this myself (though in my case it was TRUE/FALSE values). Here's how I solved it:
- Created a new calculated field called "Achieved or Missed" (you should use something appropriate for your data), assign it a numerical value of '1' whenever the original field's value is 'Yes' or a numerical value of '0' whenever the original field's value is 'No'
- Create 1 new measure called '% Achieved' --- SUM('Achieved or Missed') / COUNT('Achieved or Missed')
- Create 1 new measure called '% Missed' --- (COUNT('Achieved or Missed') - SUM('Achieved or Missed')) / COUNT('Achieved or Missed')
- Use a Stacked Bar (or Column) chart and plot the 2 measures.
Thanks Phil. Clever approach, I've gotten closer based on your input, so thanks. The multiple years are adding to the complexity for this novice. I'll keep at it!
Frank,
Glad it helped. :)
As to the 'Years' ... if I understood your data correctly, you should be able to put that column in the 'Axis' of the Stacked Bar chart and have it display the Yes/No % bar for each individual year.
