By the end of this chapter you will be able to:

• Set up aggregate attributes on pipes
• Read aggregate attributes from pipes into a table

Set up aggregate attributes on pipes

In this exercise you will create a report of total and average last billed amounts, aggregated by Region.

In your Introduction to Modelling analysis model, add a new table:

1. Hover over Customers in England, and press  Create New Table.
2. Give the new table the Name: Customer Summary by Region
3.  Apply the table settings.
4. Double-click on the pipe between your Customers in England table and Customer Summary by Region table to open its settings.
5. Go to the Sort/Group section:
   
   • Press  Show Attributes to open the list of attributes, and drag the attribute Region into the list of Sort/Group attributes for the pipe.
6. Go to the Aggregate Attributes section:
   1. Press  Add New to add a new aggregate attribute and set:
      
      • Aggregate Function: Count
      • Leave the Attribute blank
      • New Attribute Name: CountRecords
      • Press  Apply and Close.
   2. Add another aggregate attribute:
      • Press  Show Attributes in the Aggregate Attributes section.
      • Drag the attribute BILLAMOUNT into the aggregate attributes list
      • Note that this already uses the attribute function Sum – leave this as it is.
      • Update the name of the aggregate value:
        • Double-click on the new aggregate attribute.
        • Update the Name to TotalLastBillAmount
        • Press  Apply and Close.
   3. Add another aggregate attribute:
      • Press  Show attributes for <table>
        
      • Drag the attribute BILLAMOUNT into the aggregate attributes list
      • Update the details for this new aggregate attribute:
        • Double-click on it
        • Update the Aggregate Function to Average.
        • Update the Name to AvgLastBillAmount
        • Press  Apply and Close.

Configure your output table

1. Drag the attribute Region from the Sort/Group section in the pipe settings onto the table Customer Summary by Region in the model.
2. Drag the aggregate attributes you added from the Aggregate Attributes section of the pipe onto the table Customer Summary by Region.
3. In the pipe settings, press  Apply and Close.

Run analysis

1. Run analysis on Customer Summary by Region.
2. View the data in this table – check that the aggregated report has been generated correctly, like the image below:
3. 
4. In the model window, click  Save.