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

pbower
pbower Member
edited February 2023 in Charting

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?

Best Answer

  • pbower
    pbower Member
    Answer ✓

    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!

Answers

  • 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?

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • It's changeable by the user.

  • 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.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • 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".


  • 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.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • pbower
    pbower Member
    Answer ✓

    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!