Aggregation by Period (ex. Month) Calculation

User_63K6C

Hello,

Working within Netsuite Analytics Warehouse workbook using the data visualization table.

Goal: to generate the total feet shipped per month.

Table 1 (by Item Code):

I have a table outlining all item ID #, with the corresponding columns"Month", "Total Quantity Shipped" and "Item Length (ft)", with the created calculation "Total Feet Shipped" (Quantity * Item Length).

Using this table, If I export the excel and run a pivot over the data by Month, I aligned to the total feet shipped value in Netsuite, therefore no issues.

Table 2 (By Month)

Issue arising in my second table where i dont explicitly list the individual item codes, I only want to see Month column and Total Feet Shipped column.

Appears NSAW is missing the order of operations when calculating as i want to calculate total feet shipped at the granular level (by item code) and aggregate all the items in the applicable month.

I have tried creating calculations using "aggregate by" or reapplying the logic through calculation to ensure the item quantity * item length for the individual items is calculated first before the aggregation of all items takes place, but I cannot get the data to algin to table 1.

Any solutions would be appreciated.

Riyaz Ali-Oracle

Hi @User_63K6C,

Try the below:

To ensure the correct order of operations, you need to calculate Total Feet Shipped at the item level first, then aggregate that result by Month.

1. Create a Calculated Field at the Granular Level

Name: Item-Level Feet Shipped
Formula:
{Quantity Shipped} * {Item Length}
This ensures the multiplication happens before any aggregation.

2. Create an Aggregated Metric
Name: Monthly Feet Shipped
Formula:
aggregate("Item-Level Feet Shipped" by "Month")
This tells NSAW to sum the pre-calculated feet shipped for each month.

3. Use This in Your Month-Level Table:
In your second table (Month only), use Monthly Feet Shipped instead of recalculating.

Thanks,
Riyaz Ali