Find the highest volume source

The Rank function can be used to find the source location with the highest volume.

Step 1: Aggregate the order quantity

Create a dynamic view that aggregates the order quantity. Filter the view to only include customer destinations.

1. Create a dynamic view to use for the aggregation. For this example, we use "1_SS1_QuantPerSDP". The "1_SS1_" prefix is used to help keep views in order in lists and "SS" stands for sourcing split.

2. In the view properties, create a join between the location_dimension_Ckey field from the destination table to the destination_location_dimension_Rkey field in the orders_full table.
3. In the dynamic view properties, add the following columns from the orders_full table:
   • source_location_dimension_Rkey
   • destination_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 SumQty. Your column specifications should now be similar to this:

   

5. Add a conditional statement to specify customer locations. Your Conditions specification should be similar to this:

   

Step 2: Rank and order by quantity

Create a dynamic view that aggregates and groups the order quantity by source, destination, and product.

1. Create a dynamic view to use for the ranking. For this example, we use 2_HVS1_Rank. The "2_HSV1_" prefix is used to help keep views in order in lists and "HSV" stands for highest source volume.
2. In the dynamic view properties, add the following columns from the 1_SS1_QuantPerSDP dynamic view:
   • source_location_dimension_Rkey
   • destination_location_dimension_Rkey
   • product_dimension_Rkey
3. Add a Rank column to rank the SumQty column. Partition the Rank column by destination and product. Your PartitionBy settings should be similar to: 

   

   Order the results in descending order by SumQty. The OrderBy settings should be similar to this:

   

4. Your Columns specification should now be similar to this:

   

Step 3: Find the location with Rank equal to 1

Next, create a new dynamic view to identify the location with the highest rank (Rank = 1).

1. Create a new dynamic view to find the highest ranking location. For this example, we name the view 2_HVS1_HighestSource.

2. In the dynamic view properties, add the following columns from the 2_HVS1_Rank dynamic view you created in Step 2:
   • source_location_dimension_Rkey
   • destination_location_dimension_Rkey
   • product_dimension_Rkey
   • SumQty
   • Rank
3. Add a conditional statement to specify the top ranking location. Your Conditions specification should be similar to this: