Configure a Combination Chart into a Pareto Chart

Last updated:
2:15pm Nov 03, 2017

Introduction to the Pareto Chart

Pareto Charts are used in Pareto Analyses. A Pareto Analysis is a technique is used to analyze data where many possible courses of action are competing for attention. It is used when analyzing data about frequency of problems or causes of problems. It shows a quantification of each item or problem and highlights the most important ones. A Pareto Chart is a type of chart that contains both bars and a line graph, where individual values are represented in descending order by bars, and the cumulative total (usually displayed as a percentage) is represented by the line.

See other charts and visulizations here.

 

Configure a Combination Chart into a Pareto Chart

 

This confgifuration will work with all versions of Spotfire.  For this example, the "Sales and Marketing.dxp" sample will be used.

The Pareto Chart is created using the Combination Chart .  After clicking on the Combination Chart icon, select a category column in the x-axis and select any 2 columns on the y-axis.  In this example,  the [Class Sales] was dragged onto the y-axis twice and the [State] column was dragged onto the x-axis.  Make sure the x-axis only used 'current filted data' by setting:  Properties > X-axis > Settings > Evaluate axis expression on: 'Current filtering only' (instead of default 'All data' which may cause the line chart to display incorrectly when filtering data).

 

Next, change one of the y-axis column aggregation to 'Cumulative Sum' which will auto generate a custom expression.

Editing the custom expression on the y-axis will display the following expression:

Sum([Class Sales]), Sum([Class Sales]) THEN Sum([Value]) OVER (AllPrevious([Axis.X]))

and the visual should look like this...

Sort x-axis by the calulation column -- Sum(Class Sales) in this example -- located in the visualization properties.

will re-order all bars including the cumulative column.

In order to configure this combination chart into a Pareto the, the cumulative calculation needs to be edited from the y-axis.  Right-click over y-axis selector-->Custom Expression...:

Sum([Class Sales]), Sum([Class Sales]) THEN Sum([Value]) OVER (AllPrevious([Axis.X]))

to

Sum([Class Sales]), Sum([Class Sales]) THEN Sum([Value]) OVER (AllNext([Axis.X]))

clicking on 'OK' should result in the following visualization:

Note that the cumulative calculation still does not look as expected; this is because the compuation is still based off the original order of the [State].  By modifying the x-axis expression (right-click on x-axis --> Custom Expression...) from:

<[State]>

to 

<Sum([Class Sales])over([State]) NEST [State]>

will re-order the original sequence to include the height of the bars into the label of the bars to show this...

Now simply by changing the cumulative bar to show as a line gets you closer to a Pareto Chart.

 

Finish up the Pareto Chart

Select Properties-->Y-axis --> 'Multiple Scales', allows for each y-axis to have its own axis.

Edit the Y-axis custom expression to turn the cumulative sum into a percentage by changing the custom expression from:

Sum([Class Sales]), Sum([Class Sales]) THEN Sum([Value]) OVER (AllNext([Axis.X]))

to

Sum([Class Sales]) as [Sales], Sum([Class Sales]) THEN Sum([Value]) OVER (AllNext([Axis.X])) / (Sum([Value]) OVER (All([Axis.X]))) as [Cum%]

Display the [Cum%] as a percentage in Properties-->Formating-->Y:Cum% -->Percentage.

Finally, also change the label orientation on the x-axis and hide the numbers by move them down and the results should be a Pareto that looks like this...

 

Feedback (3)



Hi Hector, nice post but i need some help if you please.

I try this Sum([Valor_Pretendido])over([Pedido]) NEST [Pedido]

and the return is not valid expression after NEST

I already checked the X axis filtering to current filter only.

Dou you have some advice?

 

leonardostreit81 9:22am Jun. 11, 2019

I'm getting an error at this point <Sum([Class Sales])over([State]) NEST [State]>. I used <Sum([Packed Units]) over ([VAS Type]) NEST [VAS Type]> and get "Invalid Expression." Without the <> I get "Expression is not valid after NEST..." sum([Packed Units)] is being calculated on the y axis. Please advise.

csimmons 8:08am May. 01, 2018

If the line does not have a 'smooth' cumulative rise and instead shows a jagged appearance,  make sure the x-axis only used 'current filted data' by setting:  Properties > X-axis > Settings > Evaluate axis expression on: 'Current filtering only' (instead of default 'All data').

Hector Martinez 2:18pm Nov. 03, 2017