Does anyone have a good formula for a pareto chart in Birst?

Dan_Rosen · 2024-11-19T21:36:37+00:00

There was an error rendering this rich post.

Does anyone have a good formula for a pareto chart in Birst? This is often requested and we always have to take it to excel to be created. Before I spin my wheels, I figure someone may have solved this issue before with a formula to create the pareto line on top of a standard column chart.

Find more posts tagged with

Accepted answers

CindyHoskey

Hey, Dan. Here's my way of doing it.

Add your measure and your category into a report (I usually start with tabular). In my example, the measure is named "Count", so the BQL for it is [Sum: Count], and my attribute is [Type.Issue] (bad names, I know). There are 10 different Issues in the data set.
Sort by your measure descending.
The cumulative percent calculation I used is this: RSUM(10,[Sum: Count])/[Sum: Count]{[Type.Total]}

The numerator is RSUM(10,[Sum: Count]), a running sum of the measure named Count, with a window size of 10 because I have a total of 10 issues.

The denominator is [Sum: Count]{[Type.Total]} which is a positional calc.

4. Turn it into a column chart, then set the cumulative percent as a line and viola! You have a Pareto chart.

Raw data:

Pareto chart:

Hope this helps!

- Cindy

All comments

CindyHoskey

Hey, Dan. Here's my way of doing it.

Add your measure and your category into a report (I usually start with tabular). In my example, the measure is named "Count", so the BQL for it is [Sum: Count], and my attribute is [Type.Issue] (bad names, I know). There are 10 different Issues in the data set.
Sort by your measure descending.
The cumulative percent calculation I used is this: RSUM(10,[Sum: Count])/[Sum: Count]{[Type.Total]}

The numerator is RSUM(10,[Sum: Count]), a running sum of the measure named Count, with a window size of 10 because I have a total of 10 issues.

The denominator is [Sum: Count]{[Type.Total]} which is a positional calc.

4. Turn it into a column chart, then set the cumulative percent as a line and viola! You have a Pareto chart.

Raw data:

Pareto chart:

Hope this helps!

- Cindy

Dan_Rosen

https://community.developer.infor.com/discussion/comment/638#Comment_638

This is perfect Cindy, thank you. Awesome stuff!

Quick question as I haven't worked with RSUM before - any downside to picking a large number like 1 million as the window size instead of trying to calculate an exact number of records? Seems to work based on a quick test.

CindyHoskey

https://community.developer.infor.com/discussion/comment/639#Comment_639

I'm not aware of any downsides but keep in mind that it's not the number of data points before aggregating, it's the number of categories. I doubt that a chart with a million categories would be useful.

Dan_Rosen

https://community.developer.infor.com/discussion/comment/640#Comment_640

Got it, thanks!

Dan_Rosen

https://community.developer.infor.com/discussion/comment/638#Comment_638

One thing I noticed is that adding a secondary sort by the category can be useful. I had a few cases where the value of the measure was the same for two records, but the column chart was sorting the records in a way inconsistent to the order that the running sum was being applied, so my line was doing a little up down up. I realized this when I saw that the table chart was ordered differently than the column chart for those records. Adding the sort on category resolved this as it forced the evaluation of running sum and the column chart to maintain the same order of the categories with equal values