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