CoPilot: How to Create Analytics

Create Your Chart

Click the analytics tab on the left menu bar.

Click create new chart.

Give your chart a title and description.

Know Your Data

Select the table that will source the data for you chart.

Real Time Flag

The Real Time Flag limits results to the most recent record for the selected date time field based on the the selected combination of unique keys. If your table contains data that is appended everyday by recipe run, the table will have many duplicate records. We want to eliminate the duplicates in the data by applying the Real Time Flag. If you know that the data in the table does not contain duplicates, you do not need to apply the Real Time Flag.

This requires you to know:

1. The datetime insert column for your table (will be BQInsertTimeUTC in almost all cases)

2. The combination of KEY columns for your table

Examples of key columns in the Briq Tables would be:

Table	Key Columns
Change Order	Change order ID, PojectUniqueID
Project Financials Snapshot	ProjectUniqueID, EOPerioddate
Project Financials Breakdown Snapshot	ProjectUniqueID, Cost Code, Phase, Cost Type, EOPerioddate
GL Account Master	GL Account ID

If the data is being snapshotted by the End of Period Date (as the tables ending with snapshot are) EOPerioddate would also act as a key, as we want to capture the unique record by period.

In this example, I will create a chart using the Project Financials Snapshot table. (This table might be called Job Status Summary in your environment if you are a legacy client). The Real Time Flag would be set up as follows:

Pick Your View Type

The view type determines whether the end result will be a chart (bar graph, line graph, etc) a table, a metric, or a pivot table.

View Type: Chart

When you select view type chart, you will have to pick the chart type. This allows you to pick your chart style (Area, Line, Column, Bar etc).

View Type: Chart - X- Axis

After picking the chart type, you need to select the column on the X Axis. This will be the column that the data is aggregated by along the x axis. For the example "Profit over Period", the goal of this chart is to have Profit summarized over End of Period Date. The X Axis will be "EOPerioddate".

After selecting the x axis column, turn on the "group by column" toggle. This ensures that data is aggregated by the x axis column. 

Order by column is on by default, this ensures that the values on the x axis are in order (ascending or descending).

Default date range applies if the x axis is a date column, and allows you to control the duration of time that is displayed on the chart by default (all time, 1 year, year to date, etc)

Fill in the X Axis title, type and unit if desired, these fields are optional.

View Type: Chart - Series

The series will be the Y Axis data. A series gets added by default. You can edit the existing series or create a new one. 

In the column, select the column you want to display on the chart. The example chart here is "Profit over Period", so I am selecting the "CurrentProjectedProfit" column. It is important to know which columns your desired data is in.

The series name should reflect the column selected, for example "Profit". Note, you cannot have spaces in the series name.

The aggregation type is the aggregation that will get applied to the column: sum, max, avg. Most cases would use sum.

The series type is how the series will display: line, spline (smooth line), or column. Note you can have multiple series on one chart and can have  both lines and columns.

The series label is optional, this would represent the units being used, for example dollars.

Round Values: You can adjust if the data points on the series display decimals.

Customize Series: You can change the color of the series.

For Secondary Axis: If a secondary axis is applied to the chart the series will associate to the secondary axis.

If you want the series to be grouped by another column, for example, we wanted the profit line chart to have one line for each project, use the "Group by Column". Note, you can only use this feature when you have one series.

When making adjustments in the configuration, you may have to resave the series to see the changes.

View Type: Chart - Threshold Line

Adding a threshold line allows you to add to static line to a chart that can act as a static target.

When you have completed your chart, hit save in the top right.

View Type: Table

The table chart type will give you an interactive table of the data.

You can select what columns you want to display using the "edit columns".

You can also use the edit columns option to rename fields and change data types.

When you are done with your table, hit save in the top right corner.

View Type: Metric

A metric will display a card with a single value.

To create a metric, set the view type to metric. Select the column you would like to display. Title the metric and optionally put a label. Select how you would like the aggregate the data. The data type of the column can be Float64, if you are displaying a number. There are options to display decimals, whole number, a percentage value or currency.

If you are setting up a metric that you want to display a string, like a project name or region, select the column, then select NA as the aggregation type, then select String as the data type. This is useful to have on a dashboard that has a filter for example Project or Region and you want the selection to display on the dashboard. Without a dashboard filter being applied the metric returns the first available value.

When you are finished with the metric, hit save.

View Type: Pivot Table

To create a pivot table set the view type to pivot table. Pivot tables are configured on the pivot table themselves. On "Fields" drag the desired columns into the rows, columns and values.

You can select the header of the row to filter the data.

 Under format, you can format cells and apply conditional formatting.

There is also the option to toggle the pivot table between a chart and the grid view.

When you have finished your chart, hit the save button on the pivot table table AND the save button on the chart tool in the top right.