How do I build a percent completion pie chart over time?

pbower

I'm trying to building a pie chart that shows the percentage completion for a survey that never closes. Respondents are both added and surveys are completed over time. Some of our surveys have been running for years. Any way you do this is flawed, but we feel the best way to do this is to add up the number of respondents that have been completed in the time period and divide by the number of respondents that were "listed" (added). So in the image below, Respondents 1 and 3 would count for completes, but only 2 and 3 would count for lists, so a completion rate of 50%.

I tried building an ETL job to aggregate these numbers into something like this.

But in the pie chart I need to display complete and not complete and I can't figure out how to calculate non-complete. Here is what I have at the moment, but it should say Complete Count 15 and Not Complete Count 71 (86-15).

So maybe this isn't the ETL data structure I need. Can anyone help?

pbower

Ok. I couldn't figure out how to get this to work using a pie chart, but I was able to get what I needed.

Data structure changed slightly

I ended up using a Radial progress bar. I created two beast modes for Listed and Completed.

Listed CASE WHEN `Type` = 'List Date' THEN 1 END

Completed CASE WHEN `Type` = 'Completed Date' THEN 1 END

In the radial progress bar I set Gauge Values to Sum of Completed, and Target Value to Sum of Listed.

I then created a Single Value card for Listed, Completed and Not Completed. Listed and Completed were easy. I just added a filter for the appropriate type.

And for Not Complete, I used a variation on the beast mode you suggested.

COUNT(CASE WHEN `Type` = 'List Date' THEN 1 END) - COUNT(CASE WHEN `Type` = 'Completed Date' THEN 1 END)

Here is the end result.

Thanks for your help Grant!

GrantSmith

What does your date range look like? Is it always the current month or is this changeable by the end user to display other months?

pbower

It's changeable by the user.

GrantSmith

I'd recommend reformatting / pivoting your data into a structure such that you have a date and the type of date that it is.

Respondent, Type, Date

Respondent 1, List, 12/31/2022

Respondent 1, Complete, 1/3/2023

Respondent 2, List, 1/1/20223

Respondent 3, List, 1/2/2023

Respondent 3, Complete, 1/4/2023

Then do your grouping based on the Date and Type to calculate the amounts for your chart.

pbower

Thanks for your help Grant. I feel like I'm close. But I still don't know how to calculate or display the Not Complete number.

Here's what the data looks like now.

And the Pie Chart. Group by Type and Pie Value is Count of Respondent ID. How can I show "Completed" and "Not Complete" instead of "List" and "Completed".

GrantSmith

You can utilize a beast mode and subtract the two:

COUNT(CASE WHEN `Type` = 'List' THEN `Date` END) - COUNT(CASE WHEN `Type` = 'Completed' THEN `Date` END)

Then also have a beast mode for the completed count:

COUNT(CASE WHEN `Type` = 'Completed' THEN `Date` END)

Use these two beast modes in your pie chart instead.

pbower

Ok. I couldn't figure out how to get this to work using a pie chart, but I was able to get what I needed.

Data structure changed slightly

I ended up using a Radial progress bar. I created two beast modes for Listed and Completed.

Listed CASE WHEN `Type` = 'List Date' THEN 1 END

Completed CASE WHEN `Type` = 'Completed Date' THEN 1 END

In the radial progress bar I set Gauge Values to Sum of Completed, and Target Value to Sum of Listed.

I then created a Single Value card for Listed, Completed and Not Completed. Listed and Completed were easy. I just added a filter for the appropriate type.

And for Not Complete, I used a variation on the beast mode you suggested.

COUNT(CASE WHEN `Type` = 'List Date' THEN 1 END) - COUNT(CASE WHEN `Type` = 'Completed Date' THEN 1 END)

Here is the end result.

Thanks for your help Grant!