Cosmic Frog Data Tables – Row Grouping, Aggregation, and Pivoting

Cosmic Frog users can now perform additional quick analyses on their supply chain models’ input and output data through Cosmic Frog’s new grid features. This functionality enables users to easily apply different types of grouping and aggregation to their data, while also allowing users to view their data in a pivoted format. Think for example of the following use cases:

• Perform a quick check of the total demand / shipment quantity specified in the customer demand / customer orders / shipments input tables, in total or for example by product or customer (region).
• Get the average distance and / or time for certain types of flow and / or by mode from the flow summary output tables.
• Count the number of times inventory is below or above a certain level.
• Find the minimum and / or maximum utilization of facilities, transportation assets, or work centers.

In this documentation we will cover how grids can be configured to use these new features, show several additional examples, and conclude with a few pointers for effective use of these features.

How to Access the New Grid Features

These new grid features can be accessed from the “Columns” section on the side bar on the right-hand side of input and output tables while in the Data module of Cosmic Frog:

1. We are on the Optimization Flow Summary output table, a network optimization (Neo) output table.
2. Click on “Columns” on the right-hand side of the table.
3. This opens the form on which row grouping, aggregation, and pivot grids can be configured.
4. There are 3 modes available to the user:
   1. Row grouping – when the Pivot Mode dial is off and only the Row Groups part of the Columns configuration panel is used.
   2. Aggregated table mode – when the Pivot Mode dial is off and both the Row Groups and ∑ Values areas of the Columns configuration panel are being used.
   3. Pivot mode – when the Pivot Mode dial is on (moved to the right) and the Row Groups, ∑ Values, and Column Labels areas of the Columns configuration panel are being used.

Alternatively, users can also start grouping and subsequently aggregating by right clicking on the column names in the table grid:

1. Right click on the name of the column to be used to group by, in this case on the ScenarioName column.
2. Select “Group by ScenarioName” from the context menu.

We will first cover Row Grouping, then Aggregated Table Mode, and finally Pivot Mode.

Row Grouping

Using the row grouping functionality allows users to select 1 column in an input or output table by which all the records in the table will be grouped. These groups of records can be collapsed and expanded as desired to review the data. In the following screenshot the row grouping feature is used to compare the sources of a certain finished good in a particular period for 1 scenario:

1. We are on the Optimization Flow Summary table, which is an optimization (Neo) output table.
2. The table has filters applied to the scenario name field, the departing period name field, and the product name field. This filters the table for 1 specific scenario, 1 specific period (YEAR1), and 1 specific finished good (FG_SWI).
3. The Origin Name field is used as the row to group by (right click on the field and choose the “Group by OriginName” option or click on Columns on the right-hand side of the table and configure the OriginName field as the Row Group).
4. Now all records are grouped by each Origin Name that exists in the filtered data. By default, the groups are collapsed like this one shown for DC_2. In parenthesis the number of records that are grouped together (i.e. that have DC_2 as the origin) is listed, 11 in this case.
5. For DC_1 and DC_3 the records (9 for DC_1 and 3 for DC_3) have been expanded as the user wanted to compare the flows of FG_SWI in YEAR1 for these 2 distribution centers.

When clicking on Columns on the right hand-side of the table to open the row grouping / aggregated table / pivot grid configuration pane shows the configuration for this row grouping:

1. Pivot Mode is left turned off.
2. The Origin Name field is used as the Row Group.
3. The ∑ Values area is left blank as no aggregation of data is needed when just grouping by a row.
4. These 4 fields are being shown in the table, besides the grouped Origin Name: Destination Name, Product Name, Mode Name, and Flow Quantity.

Aggregated Table Mode

Once a table is grouped by a field, a next step can be to aggregate one or multiple columns by this grouped field. When this is done, we call this aggregated table mode. Different types of aggregation are available to the user, which will be discussed in this section.

‍

When configuring the grid through the configuration panel that comes up when clicking on Columns on the right-hand side of input & output tables, several options are available to help users find field names quickly and turn multiple on/off simultaneously:

1. Clicking on this checkbox will enable or disable all fields that are displayed in the list below simultaneously.
2. Type part of a field name in the search box to filter for fields containing the text to quickly find field(s) of interest.

To configure the grid, fields can be dragged and dropped:

1. Click on the icon with 12 little dots (in between the checkbox and the field name) and hold the mouse down to drag the field into 1 of the configuration boxes below. In the screenshot above the Scenario Name field is being dragged.
2. Drop the field into the Row Groups area if the field should be used for aggregating the rows in the grid. In the case of dropping the Scenario Name field here it means that whichever other fields we enable, they will be aggregated by scenario. Note that currently a maximum of 1 field can be used for Row Groups, more to come in future releases!
3. Drop the field into the ∑ Values area if the field should be aggregated by the row groups. Multiple fields can be dragged into this area.

Alternatively, instead of dragging and dropping, user can also right-click on the field(s) of interest to add them to the configuration areas. This can be done both in the list with column names at the top of the configuration window as shown in the following screenshot, but also on the column names in the grid itself (which we have seen an example of in the “How to Access the New Grid Features” section above):

In the screenshot above (taken with Pivot Mode on which is why the Column Labels area is also visible), user right-clicked on the Flow Volume field and now user can choose to add it to the Row Groups area (“Group by FlowVolume”), to the ∑ Values area (“Add FlowVolume to values”), or to the Column Labels area (“Add FlowVolume to labels”).

‍

The next screenshot shows the result of a configured aggregated table grid:

1. Still on the Optimization Flow Summary output table and in grid mode, user has added Scenario Name to the Row Groups configuration area.
2. The Flow Volume field was added to the ∑ Values area, by default it will be summed as it is a numeric field (see below).
3. As no other fields are used for this aggregated table grid, the result is a table with 2 columns. The first column is for Scenario Name, the row groups field. Note that for each scenario the number of rows that were aggregated is listed in parentheses.
4. The second field in the table is for the summed Flow Volume. So, we are looking at the total flow volume by scenario here.

When adding numeric fields to the ∑ Values area, the following aggregation options are available to the user:

1. Click on the icon with 12 little dots to bring up the aggregation options drop-down list.
2. The options are:
   1. Avg – calculates the straight average of the field (sum of all values divided by the number of values).
   2. Sum – calculates the sum of all values.
   3. Max – finds the maximum value and shows this.
   4. Min – finds the minimum value and shows this.
   5. Count (not shown in screenshot) – counts the number of values.
   6. First (not shown in screenshot) – the lowest numeric value found for numeric fields (i.e. same as Min) and the first value found for text fields when sorted alphabetically
   7. Last (not shown in screenshot) – the highest numeric value found for numeric fields (i.e. the same as Max) and the last value found for text fields when sorted alphabetically.

For non-numeric fields, only the last 3 options are available as aggregations:

1. Here we look at the Mode Name field as the example.
2. As this is a text field, only count, first and last are available as the aggregation options.

When adding an aggregation field through right-clicking on a field name in the grid, it looks as follows. User right-clicked on a numerical field, Transportation Cost, here:

When filters are applied to the table, these are still applied when the table is being grouped by rows, aggregated, or pivoted:

1. User has clicked on Filters in the right-hand side bar of the Optimization Flow Summary output table to bring up the Filters configuration form.
2. A filter is set up to only show flows to customers (the filter is: FlowType field Contains “customer”).
3. We can see at the right top of the table grid that a filter is active and which field(s) it is applied to.
4. This changes the aggregated grid as the number of rows is now reduced as compared to the previous screenshot of summed flow volume by scenario name. The Baseline now shows 580 rows vs 690 before.
5. The summed flow volume now represents the flow volume to all customers and for Baseline this amounts to 954,885. When not filtering for customer flows, the total Baseline flow volume was over 6.3M, see the earlier screenshot above.

It was mentioned above that the number in parentheses after the scenario name represents the number of rows that the aggregation was applied to. We can expand this by clicking on the greater than (>) icon to view the individual rows that make up the aggregation:

1. In this aggregated grid, user has clicked on the greater than icon (>) to the left of the Baseline scenario name to expand the 131 rows that make up the aggregation. Clicking on this icon again, which has turned into an upside-down caret icon, will collapse the rows again.
2. It is again an aggregated grid of summed flow volume by scenario on the Optimization Flow Summary output table, but additional fields are shown: Origin Name, Destination Name, and Product Name (note that in the table grid in this screenshot, product name is not visible, user needs to scroll right to see it).
3. The table has a filter applied to the Product Name field.
4. At the left top of the table, it is also indicated how many rows of the total are being shown in the grid.
5. The top rows of the 131 that make up the rows for the Baseline aggregation can be seen here.

Pivot Mode

When users turn on pivot mode, an extra configuration area named Column Labels becomes available in addition to the Row Groups and ∑ Values areas:

1. User has turned Pivot Mode on. We are still looking at an example on the Optimization Flow Summary output table.
2. In addition to Scenario Name as the row group and summing Flow Volume still, now the Product Name has been added to the Column Labels area of the pivot grid configuration form.
3. This results in all products that are present in the Optimization Flow Summary output table to be put across the grid as columns. (note that not all products are shown in the screenshot, user needs to scroll right to see the others).
4. A filter is applied to the Flow Type field, which is the same as we have seen above: to filter out only flows to customers.
5. We see that in the Baseline the total volume of flow to customers for product FG_FET is 236,646 and for product FG_MOZ it is 198,544.
6. In Grouped Row Mode, Aggregated Table Mode and Pivot Mode, fields can be removed from the configuration areas by clicking on the x icon on the right-hand side of the field name. Alternatively, user can right-click on the field name in the list and choose “Un-Group by ScenarioName”, etc.

Another example to show the total volumes of different flow types, filtered for finished goods, by scenario is shown in the next screenshot:

1. Flow Type has now been added as the Column Label field.
2. The 2 Flow Types that are present in the Optimization Flow Summary output table are listed across as the columns: Replenishment and CustomerFulfillment.
3. The table has a filter applied to Product Name, which is to filter out Finished Goods (products with a Product Name that starts with FG_; not shown in the screenshot).
4. In the “No demand yr5” scenario, we see that the total replenishment flow volume of finished goods is 130,891 and the total flow volume of finished goods to customers (the Customer Fulfillment flow type) is the same amount: 130,891.

Additional Examples

So far, we have only looked at using the new grid features on the Optimization Flow Summary output table. Here, we will show some additional examples on different input and output tables.

‍

In this first additional example, a pivot grid is configured to show the total production quantity for each facility by scenario:

1. We are on the Optimization Product Summary output table, another network optimization (neo) output table.
2. Pivot Mode is turned on.
3. The configuration of the pivot grid is as follows:
   1. We again have set up Scenario Name to be the row group field.
   2. Summed production quantity will be shown as the values in the grid.
   3. The Facility Name field is used for Column Labels.

In the next example, we will show how to configure a pivot grid to do a quick check on the shipment quantities: how much the backhaul vs linehaul quantity is and how much of each is set to Include vs Exclude:

1. We are on the Shipments input table, a table used by the transportation optimization (Hopper) and simulation (Throg) engines.
2. Pivot Mode is turned on.
3. The pivot grid is configured as follows:
   1. The Notes field, which for each shipment record contains either “Backhaul” or “Linehaul” is used as the row group field.
   2. The summed Quantity is shown as the values in the grid.
   3. The Status field, which is set to either Include or Exclude, is used for column labels.
4. For Backhaul we see that 32,047 units are included in the input data and 3,654 units are set to Exclude.

In the following 2 examples, we are doing some quick analysis on the Simulation Inventory On Hand Report, a simulation (Throg) output table containing granular details on the inventory levels by location and product over time. In the first of these 2 examples, we want to see the average inventory by location and product for a specific scenario:

1. We are on the Simulation Inventory On Hand Report simulation output table.
2. Pivot Mode is turned on and the configuration is as follows:
   1. Facility Name is used as the row group.
   2. Inventory On Hand Quantity is used for values and the type of aggregation is to calculate the average.
   3. Product Name is used as column labels.
3. A filter (not shown) is applied to the Scenario Name field, so the values shown in the pivot grid are those for 1 specific scenario, the Baseline in this case.
4. We see that there are 2,101 records for inventory on hand at the DC_VA location. The average inventory quantity for Product_4 at this location is 778 units, for Product_1 449 units, and for Product_3 314 units.

In the next example, we want to see how often products stock out at the different facilities in the Baseline scenario:

1. The pivot grid configuration is the same as in the previous example, except that now instead of summing the inventory on hand quantity, we are counting the number of occurrences.
2. An additional filter is applied (not shown) which filters for the inventory on hand quantity being equal to 0. So, the count results in how often the inventory on hand is 0 for the specific location-product combination, in the Baseline scenario, as that filter is still applied too.
3. At DC_AZ, we see that Product_4 stocks out 11 times during the simulation horizon in the Baseline scenario, Product_1 117 times and Product_3 186 times.

The last 2 examples in this section show 2 different views of Greenfield (Triad) outputs. The first example shows the average transport distance and time by scenario:

1. The table we are on is the Optimization Greenfield Flow Summary table, an output table of the Greenfield (Triad) engine.
2. Pivot mode is not turned on, so we are in aggregated table mode.
3. Scenario Name is added as the row group and the averages of the Transport Distance and Transport Time fields are used for the values.
4. Between the “7 Optimal Facilities Paris Fixed” and “Cost Optimized” scenarios, we see a reduction in both average transport distance and average transport time.

Lastly, we want to look, by scenario, how much of the quantity delivered to customers falls in the 300 miles, 500 miles, and 750 miles service bands:

1. We are still on the Optimization Greenfield Flow Summary output table and have turned Pivot Mode on now.
2. As Row Group we have selected Scenario Name again, values shown are summed flow quantities and Service Band Names will be across the grid as columns.
3. We see that the “Cost Optimized” and “Cost Optimized Increased Demand” scenarios have the highest amounts of delivered quantity in the shortest service band of 300 miles.

Final Notes

Please take note of following to make working with Row Grouping, Aggregated Table Grids and Pivot Grids as effective as possible:

• A quick way to remove columns from a grouped or aggregated grid: while grouping and/or aggregating columns, if Pivot Mode is enabled but no Column Labels are specified, all columns that are not currently used for grouping or aggregation will be hidden.
• When a table has been grouped by a row, has been aggregated, or a pivot grid has been configured on it, these persist. When switching view to another table, going into another Cosmic Frog module or out of a model and coming back to it later, the configured grid will still be the same as how it was last configured.
• As mentioned above, if a filter is applied, that applies to grouped tables, aggregated tables, and pivot grids too. Columns that are filtered out will show values of 0 in this case and are not entirely removed from the grid. For example: if there are 2 products in the model, 1 product is filtered out, and Product Name is used as Column Labels in a Pivot Grid, then the 2 products will be shown as the columns and the values of the product that is not included in the filter are all set to 0. This does not mean that all the values for this product would be 0 if the product was not filtered out.
• As also mentioned above, currently 1 field can be used as a row group. Users can add multiple fields to be used as column labels, but currently they are not applied in a hierarchical manner.
• The same field cannot be used multiple times in a pivot grid.
• To get back to normal table mode, user needs to remove the fields from the Row Groups, ∑ Values, and Column Labels areas by clicking on the x icons or right-clicking on the fields in the list and choose the option to remove the field from the area it is used in.
• The export to Excel/CSV features are not available when in grouped table, aggregated table, or pivot grid mode, but users can use the “Copy with group headers” functionality to copy-paste the grid into for example Excel:

1. Put the cursor in a cell of the pivot grid, then use Ctrl + A to select the whole pivot grid.
2. Right-click on the pivot grid and select the “Copy with Group Headers” option from the menu. When pasting into Excel, it will look similar to following screenshot: