Create a running percent of total (Pareto analysis)

The CumulativeSum function can be used to create a running percent of total (Pareto analysis). Pareto analysis identifies the areas that have the largest effect on results.

Step 1: Aggregate the order quantity to product and DC level

Create a dynamic view that aggregates the order quantity to a product and DC level.

1. Create a dynamic view to use for the aggregation. For this example, we use "0_RPOT_QuantPerProductLocation". The "0_RPOT" prefix is used to help keep views in order in lists and "RPOT" stands for Running Percent of Total.

2. In the view properties, create a join between the source_location_dimension_Rkey field from the orders_full table to the location_dimension_Ckey field in the locations_dimension table.
3. In the dynamic view properties, add the following columns from the orders_full table:
   • source_location_dimension_Rkey
   • product_dimension_Rkey
   • item_quantity
4. For the item_quantity column, add the Sum function to aggregate the quantity. Name the column SumQuant. Your column specifications should now be similar to this:

   

5. Add a Rank statement to avoid the CumulativeSum behavior when there is a tie between rows. The CumulativeSum function groups members with the same value, which may cause the function to calculate values differently than expected when there are duplicate values. The CumulativeSum function is used in the next view. Order the Rank function by item_quantity. Your column specifications should be similar to this:
   

   

   The OrderBy settings should be similar to this:

   
6. Add a conditional statement to specify DCs. Your Conditions specification should be similar to this:

   

Step 2: Create a running percent of total for each DC

Next, create a new dynamic view to include a running percent of total for each DC by adding the following statements to the dynamic view, where "0_RPOT_QuantPerProductLocation" is the name of the dynamic view created in Step 1, and SumQuant is the column being summed in that view. The CumulativeSum function here is used to aggregate the DC totals. The PartitionBy function separates the summations by location. The OrderBy function orders the results by the SumQuant column.

1. Create a new dynamic view to use for the running percentage. For this example, we use "0_RPOT_RunningPercent". The "0_RPOT" prefix is used to help keep views in order in lists and "RPOT" stands for Running Percent of Total.

2. In the dynamic view properties, add the following columns from the "0_RPOT_QuantPerProductLocation" dynamic view you created in Step 1:
   • source_location_dimension_Rkey
   • product_dimension_Rkey
   • SumQuant
3.  Add this statement in the view's Columns section to aggregate totals by location.
   SumPerLoc: [0_RPOT_QuantPerProductLocation].SumQuant.Sum().PartitionBy([0_RPOT_QuantPerProductLocation].source_location_dimension_Rkey)
4.  Add this statement in the view's Columns section to create cumulative totals by location.
   CumSumPerLoc: [0_RPOT_QuantPerProductLocation].SumQuant.CumulativeSum().PartitionBy([0_RPOT_QuantPerProductLocation].source_location_dimension_Rkey).OrderBy([0_RPOT_QuantPerProductLocation].SumQuant)
5. Add this statement in the view's Columns section to create a cumulative percent of total by DC.
   RunningPercentOfTotal: [0_RPOT_QuantPerProductLocation].SumQuant.CumulativeSum().PartitionBy([0_RPOT_QuantPerProductLocation].source_location_dimension_Rkey).OrderBy([0_RPOT_QuantPerProductLocation].SumQuant) / [0_RPOT_QuantPerProductLocation].SumQuant.Sum().PartitionBy([0_RPOT_QuantPerProductLocation].source_location_dimension_Rkey)

   The Columns section of the view should now look similar to this:

   

Note: You can use this view in conjunction with If statements to create a Pareto ABC calculation.

Alternatively, you can create a running cumulative percent of the entire table.