Summarize data - Data360_Analyze - Latest

Calculate profit

1. Following on from the previous steps in Merging data, locate the Calculate Fields node in the Nodes panel and drag it onto the canvas. Rename the node to "Calculate Profit".
2. Connect the output from the "Merge with Product Info"Lookup node to the input of the Calculate Fields node.
3. Select the Calculate Fields node, and add a new field by using the "Calculated Fields" table.
4. Enter "Profit" for the name of the field.
5. Change the value of Type to "double".
6. Enter Price - Cost as the value of the expression.
7. Run the node, and click the Calculated Fields output pin. You'll see a new field called Profit in the data viewer, which is equal to the Price minus the Cost for each record.

Aggregate the data

It will be helpful to us to find out some details on each of our products. We can do this by grouping together occurrences of values in a field by using an Aggregate node. In our example, we are going to use the node to group together records by Product, so we will have one record in the output for "Hot Tea", one for "Large Coffee", and so on.

1. Locate the Aggregate node in the Nodes panel and drag it onto the canvas.
2. Connect the output from the "Calculate Profit"node to the input of the Aggregate node.
3. From the Properties panel rename the Aggregate node to "Revenue Analysis".
4. The GroupBy property is used to identify how to group data for further processing. In the GroupBy property, select the "Product" field.
5. Using the Grid in the Operations property, select the aggregate operations that you want to use to group your data:
   • On the first row, select the Count operation and the "Product" field. Rename the output field name to "totalTransaction". This will count the number of records in a group and output the result to a field named "totalTransaction".
   • On the next row, select the Sum operation and the "Price" field. Rename the output field name to "totalProductSell". This will sum the value of "Price" for each record in a group and output the result to a field named "totalProductSell".

1. Run the node.
2. Click the output pin record count to view the output records in the data viewer. In addition to the new "totalTransaction" and "totalProductSell" fields, the "Product" field will also be included in the output as this is referenced in the GroupBy property.
3. Save your changes to the data flow before continuing.

Next steps

Now that you have summarized your data, it is ready to be exported or published. For the next step, see Publishing data.