Forum Discussion

KelseyPoole__MP's avatar
KelseyPoole__MP
Qrew Member
2 years ago

Reports & Charts - Creating a Pareto Chart

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 as...
KelseyPoole__MP's avatar
KelseyPoole__MP
Qrew Member
2 years ago
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
------------------------------
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    2 years ago
    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
    ------------------------------
    • KelseyPoole__MP's avatar
      KelseyPoole__MP
      Qrew Member
      2 years ago

      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
      ------------------------------
      • AdamKeever1's avatar
        AdamKeever1
        Qrew Commander
        2 years ago

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

        Pareto job aid

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