Discussions

 View Only
  • 1.  Reports & Charts - Creating a Pareto Chart

    Posted 11-09-2022 18:39
    I am trying to create a Pareto Chart. I've selected a "Line and Bar" for the Chart Type. I am trying to figure out how to add the line graph that showcases the cumulative percentage of events.

    I assume it will need to be added into the "Report Formulas"...I keep wracking my brain trying to figure it out.

    Any advise or help?

    ------------------------------
    Kelsey Poole
    ------------------------------


  • 2.  RE: Reports & Charts - Creating a Pareto Chart

    Posted 11-09-2022 19:07
    It's probably more complicated than that - is this what they look like?

    https://asq.org/quality-resources/pareto

    Mark

    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------



  • 3.  RE: Reports & Charts - Creating a Pareto Chart

    Posted 11-10-2022 12:14
    Hello Mark!

    Yes, the Pareto chart I am wanting to create would follow the example below:



    Within QuickBase, I select the option for the bars to be organized from "High to Low", which gets them in the right order. But the Line portion of the Pareto Chart is supposed to be a cumulative percentage. 

    I typically use Excel to create a Pareto Chart. I would order the categories from most frequent to least frequent. Total up the Number of Opportunities to establish the Denominator that is used to calculate the percentage. Then I would calculate the "Collection" (which would be the numerator for the percentage). The Collection equals the Number of Opportunities + the Number of Opportunities for the previous Metric.

    For example:
    • Metric #1 has 6 Total opportunities. Since Metric#1 is the most frequent occurring Metric, the Collection = 6.
    • Metric#2 has a total of 3 Opportunities. The Collection for Metric#2 is going to equal 3+6, which is 9.
    • Metric#3 has a total of 2 Opportunities. The Collection for Metric#3 is going to equal 2+9, which is 11
    • The above formula would be followed for all of the Metrics.
    • To calculate the Percentage, I divided the Collection Number, by the Total Number of Opportunities for all Metrics.
      • The Total Number of Opportunities is 13
      • The Percentage for Metric#1 is calculated by dividing 6/13=46%
      • The Percentage for Metric#2 is calculated by dividing 9/13=69%
      • The Percentage for Metric#2 is calculated by dividing 11/13=85%


    We've moved all of the data into QuickBase to eliminate utilizing multiple documents to collect the data. Im hoping that we can create the pareto chart in QuickBase.

    Any tips or recommendations? 

    Thanks for the help!



    ------------------------------
    Kelsey Poole
    ------------------------------



  • 4.  RE: Reports & Charts - Creating a Pareto Chart

    Posted 11-10-2022 12:24
    Quickbase does not currently have a Pareto Chart.  That aspect of "cumulative' only exists in the waterfall type report.  


    So I see two options.   Work with either free or paid services from "The Quickbase Junkie" @Sharon Faust such as this free video https://www.quickbasejunkie.com/blog/how-to-cumulative-charts
     to calculate the cumulative field using a Formula Query.   There are also ​help text natively in Quickbase but Sharon has great examples to follow.​

    Alternatively, you could use the third party add on from Juiced Technologies called XL Docs  to click a button to create an excel sheet with the chart that you built in the Excel template.  But then you end up with an excel sheet and obviously not as elegant as just running a Quickbase report.

    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------



  • 5.  RE: Reports & Charts - Creating a Pareto Chart

    Posted 06-28-2023 18:04

    I was able to create a Pareto Chart in QuickBase :) I had to write 5 different formulas, but its perfect! The only thing left is to figure out how to filter the Pareto Chart!!!!



    ------------------------------
    Kelsey Poole
    ------------------------------



  • 6.  RE: Reports & Charts - Creating a Pareto Chart

    Posted 08-23-2023 12:15
    Edited by Adam Keever 08-23-2023 12:17

    @Kelsey Poole, MPH, CIC, thanks for posting the pareto report job aid!

    Pareto job aid

    ------------------------------
    Adam Keever
    ------------------------------