A few days ago, our support team came upon an interesting question of how to display poll results using a pie chart. In particular, how to tally results where each vote is considered a separate record and each result is recorded as text. Here's what the data source may look like:
How do we visualize this data?
The challenge most folks face with a data set like this is knowing how to tally up the results. The trick is using the Klip Editor to format this data so the dashboard understands what you want from the data. To do this, we are going to use two functions: CountDistinct and Group.
Tallying the results
The first step is to tally up the values so we can have a running total for each result type. The CountDistinct function counts or tallies each unique value in a column or array. So, if I select column B:B, I will see that I have 3 yes, 1 no, and 3 not sure votes. Notice that I am using a Slice function within the Count Distinct function. By default, the Slice function will remove the first value from your selection meaning I don't have to worry about accidently including the column header as a result in my pie chart.
Labelling the results
The next step is to label the results so we know what we are looking at in the pie chart. The Group function eliminates duplicate values from a column and can be used to reduce a list of repeating elements to only one of each name. Since column B:B also provides the description for the results, we will select that column. Again, I'm using a Slice function to remove the column header from my pie chart.
The finished chart
The end result is a simple pie chart:
What if I don't want to use a Pie Chart?
This method works well with the bar/line chart component. To use this method in a bar/line chart, assign the CountDistinct function to the first series sub-component, and then assign the Group function to the X Axis sub-component. Since you only need a single series of data in this chart, I would also invert the X and Y axis by opening the Properties tab associated with the Chart component.
Where will I encounter this type of data?
I find this type of data is common with iFormBuilder as each record is populated based on the unique identity of the person casting the vote, rather than summing up votes such as a service as PollDaddy.
Originally published May 9, 2013, updated Apr, 07 2021