Chartio is going away March 1, 2022
Documentation Charts FAQs

Use business hours for charts

It’s possible to adjust the dates of reporting in Chartio, such as differentiating between weekends and the typical Monday-Friday business days. In this example, we want to add overtime pay to the employees’s wages when they work on weekends. We have a “Shift” column that holds the date the employee worked.

In the Visual SQL interface

  1. Add the Shift column to the Query twice. For the first instance of the “Shift” column, use the Day aggregation; for the second instance, use the Day of Week aggregation. Also pull in the columns containing the employee’s name, the employee’s hourly wage, and the number of hours they worked for the day.

    Add the Shift column twice and use the Day and Day of Week aggregations

  2. To get each employee’s base pay for the day they worked, add a Formula Column, select Custom for the formula type, then use the following formula, replacing the column names with your own:

     "Hourly Wage" * "Hours Worked"

    Get the base pay by multiplying the Hourly Wage and the Hours Worked

  3. To make the purpose of this new column more clear, rename this new column to Gross Pay per Day.

  4. Now use an Apply Formula Action on the “Gross Pay per Day” column, select Custom for the formula type again, then use the following CASE statement to calculate the time-and-a-half pay that should be applied to weekends (which are the only days of the week starting with the letter S):

     case when "Day of Week: Shift" like 'S%' then 1.5*"Gross Pay per Day" else "Gross Pay per Day" end

    Calculate the overtime pay by multiplying the base pay by 1.5 if the employee worked on a weekend

In the Data Explorer interface

  1. Drag Shift into the Dimensions field twice and select different time buckets: one for the Day (or Date) of Shift and the other for Day of Week. Also pull in the columns containing the employee’s name, the employee’s hourly wage, and the number of hours they worked for the day.

    Set up your chart by dragging shift into the Dimensions field twice

  2. In the Pipeline, add a Combine Columns step to combine the hourly wages and hours worked per day columns. Select the option to hide the original columns so that the calculation will happen behind the scenes.

    Add Combine Columns step in the Pipeline

  3. Add an Edit Column step and use a CASE statement to edit the Gross Pay per Day column to add time-and-a-half pay to weekend days (which are the only days of the week starting with the letter S):

    case when "Day of Week: Shift" like 'S%' then 1.5*("Gross Pay per Day")
else "Gross Pay per Day" end

    Edit Column using a CASE statement

Need more help?