Amazon QuickSight Calculated Field Examples

Access to the ServiceTrade Data Warehouse with Amazon QuickSight is available only to ServiceTrade Enterprise customers.  For more information about ServiceTrade Enterprise, contact sales@servicetrade.com.

When visualizing your data in ServiceTrade's Data Warehouse with Amazon QuickSight, you can use calculated fields to summarize, group, filter, and transform your ServiceTrade data.  The QuickSight calculated field documentation is a great way to get started with calculated fields.  However, there are a few calculated field formulas that ServiceTrade customers commonly use that we have made available to you here as a reference.

Calculate tech total time on a job, in minutes

ifelse(isNull({job_prep_total_minutes}),0,{job_prep_total_minutes}) + ifelse(isNull({en_route_total_minutes}),0,{en_route_total_minutes}) + ifelse(isNull({on_site_total_minutes}),0,{on_site_total_minutes})

For use in:  Jobs and Tech Productivity datasets

If a tech doesn’t clock in/out while on a job, the clock data is presented as a NULL value in the data warehouse, not a 0.  This formula ignores NULL values so the math can work properly.

Tech gross profit per hour

({job_actual_margin} * {job_invoice_amount}) / ({tech_time} / 60)

For use in:  Jobs dataset

This formula makes use of the 'Calculate tech total time on a job, in minutes' formula shown above.  In this example, the calculated field for tech total time on a job is named job_actual_margin.

Quoted gross profit percent to goal

(sum(total) - sum({job_cost})) / 32000

For use in:  Quotes dataset

The divisor is the quoted gross profit goal for the timeframe by which you are summarizing in your QuickSight visualization.  In this example, we intend to summarize this data by month and assigned quote owner, so we use a divisor of 32000 because we want each quote owner to generate $32K in gross profit each month.  There is some guidance in choosing an appropriate goal in this blog post (section 5, 'Quote gross profit per administrative hour').

Quarter in which a date occurs

extract("Q",{completed_on})

For use in:  any dataset with 'datetime' fields.

This formula represents a date as the relative quarter.  For example, 4/26/2020 would become 2, because April is in the second quarter of the year.  This can be done for any part the date by a slight change to the formula.  For example, change the “Q” (quarter) to “MM” (month) and 4/26/2020 would present as 4, because April is the fourth month of the year. 

This example uses the job completed_on date field, but this formula can be applied to any 'datetime' field in the ServiceTrade data warehouse.

Group job types into categories

ifelse({job_type} = "Inspection" or {job_type} = "Priority Inspection" or {job_type} = "Repair","Inspections & Repairs",{job_type} = "Service Call" or {job_type} = "Urgent Service Call" or {job_type} = "Emergency Service Call" or {job_type} = "Priority Service Call","Service Calls","Other")

For use in:  Jobs, Invoices, Quotes, and Tech Productivity datasets

This formula uses QuickSight's ifelse operator to group job types into more general categories to represent different departments or revenue streams.  The example below groups planned work (Inspection, Priority Inspection, and Repair job types) into “Inspections & Repairs” and reactive work (Service Calls, Urgent Service Calls, Priority Service Calls, and Emergency Service Calls) into “Service Calls”.

You can adjust the formula to change the names of the groups, or the types of jobs that are classified into each group.  You can also add as many groups as you wish by adding more if/else conditions.  The very last part of the formula ("Other" in this example) will be used whenever none of the if/else conditions are met.