Generating Counts and Other Statistics

Introduction

Counting values is very useful for generating statistics within your models and there is functionality within PhixFlow to help you achieve this.

Simple Count

We can generate counts of particular attributes using a View. This means we do not need to edit the source data only how is is displayed.

Example

We have a set of company details and we want to see how many times a company name appears in the list. 

Solution

  1. Open the properties of the table holding the company data.
  2. Create a new view, this will be used to display our count:
    1. Set the Name e.g. Company Counter.
    2. Set the Display Type to Grid.
    3. Sort Order and Filters are optional for our example. See View for more details on setting these.
    4. In View Attributes click  Show or Hide Attributes
    5. From the popup box that appears, drag in the Company into the View Attributes section:
    6. Double-click this View Attribute to open its properties and set:
      •  Name to Count or something useful.

        It must be changed as we will be adding the name again later for display purposes and we cannot have two attributes with the same name.

      • Aggregate Function to Count.
      • Save all your changes.
        With the settings so far, PhixFlow will count how many names there are.
    7. Add the name of the company to the view. PhixFlow now counts how many occurrences there are of each name. 
  3. Save all your changes
  4. In the table, right-click the view you just created and select View Chart. A view of the data will display a count of all the company names.

There are other useful aggregate functions available that we can use to generate different metrics in our views, see View Attribute for more details on these.

Bespoke Counts

Where we need to generate more complex counts such as the total sum of invoices by year, we need to create additional attributes to hold the information that will be aggregated on.

Example

If we have invoice information:


From this information we would like to generate the sum of all invoices by year.

Solution

  1. Create a new attribute on the table containing the Invoice information.
    1. Name the attribute Year.
    2. Set Type to Integer.
    3. Set the expression to
      dateGet(_out.InvoiceDate, _YEAR)
      This statement will extract a year from a date attribute, see dateGet.
    4. Save your changes.
  2. Rerun Analysis on your Table and the new attribute will be populated.
  3. Create a new View on your table
    1. Set Name to Invoice by Year or something suitable.
    2. Set Display Type to Grid.
    3. You may wish to set a Sort Order by year, this is optional.
    4. In View Attributes click  Show or Hide Attributes
    5. Add the attribute that contains the invoice amount and the new Year attribute.
      Double-click the amount attribute to open its properties and set the Aggregate Function to Sum. This will sum all the amounts by year.
    6. Save your changes.
  4. In the Table right-click the view we just created and select View Chart.


It can often be useful to add a new integer attribute with the value 1 to all records, this facilitates the ability to quickly get sum totals based on other fields. For example the number of invoices by year or the number of people with the surname "Smith".

Learn More