Intro to CQL Webinar

​

​

Custom fields, filters & expressions

​

​

​

February 10th, 2021

​

Welcome to our first customer webinar!

Introductions

​

Bridget Duffey Head of Customer Success

​

bridget.duffey@charthop.com

Natalie Serrano Customer Success Manager

​

Natalie is here to help you get the most out of ChartHop; today and into the future

natalie.serrano@charthop.com

What should I leave today’s webinar with?

​

​

1. The ability to use CQL for easy filters in the org chart and data sheet
2. Skills to make you dangerous enough to create complex custom fields
3. 2 bundles of custom fields you can configure in your account
4. Tools to keep learning and teach your employees tips n tricks

​

​

Webinar Objectives & Overview

3 Sections + Live Q&A and Polls

We’ll be posting polls and questions in the Slack Community in the #cql channel

You can ask questions as they come up in the Slack Channel - Natalie will be answering in real time

(we also recommend logging into ChartHop and having it open in another window to test things out and follow along)

Format

Poll!

What is your focus area in your role?

​

(+ share community name ideas if you have them! 🤓)

​

​

​

Part 1: Basic Overview

What is CQL?

Where can you use CQL in ChartHop?

How can you use CQL in ChartHop?

​

Part 2: How-to and Practice Application

Learning the language

Understand beginner & intermediate applications (categories)

Know where to look for answers

Most popular uses

​

Part 3: Bring it to life with Use Cases

Headcount Planning Custom Fields Bundle

New Hires Custom Fields Bundle

​

Closing & Next Steps

Agenda

Let’s get started!

CQL stands for “ChartHop Query Language” or “Carrot Query Language” -- it’s ChartHop’s in house querying language which is built in Kotlin

​

What is CQL? (Technical explanation)

• Kotlin is a general purpose programming language. (meaning it can be used for a wide variety of applications)
• Kotlin is designed to be a more concise language compared to Java

​

CQL’s strength lies in its flexibility. Users are able to type out both simple and complex queries without much effort

The inspiration for CQL mostly came from Gmail filtering (with some inspiration coming from SQL)

What is CQL? (Technical explanation)

• Helps customers deeply engage with the nuts and bolts of the product and helps gain an even better understanding of how ChartHop works: helps to think in terms of data and time, and how you want to use them to accomplish your work
• Helps leverage the capabilities of the product to move faster -- with CQL, sometimes we’re able to support capabilities that haven’t even been built yet in the workflows of the product (precedes new features and helps us come up with new ideas for features)
• Helps keep us in the app (vs in spreadsheets) because you can do things that would normally never be possible in a typical HR tech application

…. And it’s delightful & fun!

Why did we make CQL?

Poll!

Have you ever used CQL in custom fields in ChartHop?

​

(+ comment with your favorite use of CQL you’ve implemented)

​

Data Sheet & Org Chart

• Custom Filtering

​

​

​

​

Custom Fields

• Smart Calculations
• Smart Buckets

​

​

​

​

Reports

• Custom Expressions

Where do you use CQL?

!dept:sales

sum{headcount} / sum{headcount date:-1y}

base & .2

Fun (& silly example)

Fun Example -- Zodiac Signs

Aries → (month(birthDate) = 3 and day(birthDate) >= 21) or (month(birthDate) = 1 and day(birthDate) <= 19)

Taurus → (month(birthDate) = 4 and day(birthDate) >= 20) or (month(birthDate) = 5 and day(birthDate) <= 20)

Gemini → (month(birthDate) = 5 and day(birthDate) >= 21) or (month(birthDate) = 6 and day(birthDate) <= 20)

Cancer → (month(birthDate) = 6 and day(birthDate) >= 21) or (month(birthDate) = 7 and day(birthDate) <= 22)

Leo → (month(birthDate) = 7 and day(birthDate) >= 23) or (month(birthDate) = 8 and day(birthDate) <= 22)

Virgo → (month(birthDate) = 8 and day(birthDate) >= 23) or (month(birthDate) = 9 and day(birthDate) <= 22)

Libra → (month(birthDate) = 9 and day(birthDate) >= 23) or (month(birthDate) = 10 and day(birthDate) <= 22)

Scorpio → (month(birthDate) = 10 and day(birthDate) >= 23) or (month(birthDate) = 11 and day(birthDate) <= 21)

Sagittarius → (month(birthDate) = 11 and day(birthDate) >= 22) or (month(birthDate) = 12 and day(birthDate) <= 21)

Capricorn → (month(birthDate) = 12 and day(birthDate) >= 22) or (month(birthDate) = 1 and day(birthDate) <= 19)

Aquarius → (month(birthDate) = 1 and day(birthDate) >= 20) or (month(birthDate) = 2 and day(birthDate) <= 18)

Pisces → (month(birthDate) = 2 and day(birthDate) >= 19) or (month(birthDate) = 3 and day(birthDate) <=20)

​

Fun Example -- Zodiac Signs

​

Interesting & practical examples

Survey!

What is the most common excel gymnastics you’ve had to do to analyze or make your people data usable?

Percent to Midpoint Heatmap

Percent to Midpoint Heatmap

Ideal Span of Control

(socIndividualVsManagerialWo.num + socLevelOfTrainingProvided.num + socStandardizedWork.num + socTechnologyAvailable.num) <=2

Ideal Span of Control

Salary Ranges & Flags

How to get started

How to get started - Numbers, arrays, and variables, oh my!

​

​

Basic Building Blocks for CQL Language

​

• How custom fields work
• Operators
• Functions
• Understand common applications

How to get started - Numbers, arrays, and variables, oh my!

​

​

What to understand about custom fields

​

Codenames

&

Camel case

🐫

💡 Pro tip: By default they’ll be created to match, but you can make your codename first and then edit your label if you want the two to be different and have a shorter codename that’s easier to use and remember (e.g. codename = baseRange but label = Employee Salary Range)

• All custom fields have a label and a codename
• Codenames are in Camel Case
• Codenames are used for using CQL in filters and fields
• Choose codenames that are easy to remember

Tenure Heatmap becomes tenureHeatmap

How to get started - Numbers, arrays, and variables, oh my!

​

​

What to understand about custom fields

​

Pick the right Data Type: Smart Calculation or Smart Bucket

💡 Pro tip: Always add a description on smart calcs explaining how they work and what they do

How to get started - Numbers, arrays, and variables, oh my!

​

​

What to understand about custom fields

​

Pick the right Data Type: Smart Calculation or Smart Bucket

• For continuous analysis
• Algebra / basic math
• To return one numerical value
• For intermediate values in complex calculations

• To make it Pretty!
• For visualizations on the org chart
• To return a range of numerical values (vs 1 number)
• To tag or group employees with multiple options
• For crosstabs in reports

EXAMPLES

• Prorated salary
• Target bonus payout

EXAMPLES

• Salary guideline buckets
• Eligibility groups

How to get started - Numbers, arrays, and variables, oh my!

​

​

What to understand about custom fields

​

Applies To: You need to choose what the field you are creating applies to. This could be:

​

• Jobs
• Open Jobs
• People in Jobs
• People

​

💡 Pro tip: Always as a description on smart calculations explaining how they work and what they do

How to get started - Numbers, arrays, and variables, oh my!

​

​

How to choose the right “Applies To”

​

Think of an astronaut in a spaceship.

​

People fields would be about the astronaut. [birth date]

​

Jobs fields would be about the spaceship. [carrot-style ship]

​

People in jobs fields would be about them flying the spaceship. [speed of ship in last race won by the astronaut]

​

Open jobs fields would be about the spaceship when not ready to fly yet. [construction time]

How to get started - Numbers, arrays, and variables, oh my!

​

​

What is an Operator?

“An operator in a programming language is a symbol that tells the compiler or interpreter to perform specific mathematical, relational or logical operation and produce final result.”

How to get started - Numbers, arrays, and variables, oh my!

​

​

Operators in ChartHop

The best way to learn ChartHop operators is to bookmark our glossary and practice building fields and filters.

​

You can use CQL for built-in fields and custom fields.

How to get started - Numbers, arrays, and variables, oh my!

​

​

Functions in ChartHop

Functions are not fields but tools to use to create expressions (e.g. the CQL code to write an if/then statement)

How to get started - Numbers, arrays, and variables, oh my!

​

​

Basic Building Blocks for CQL Language

• Operators & Filtering
• CQL Operators
• Example Functions
• Example CQL Functions
• Field Names
• Built-in fields; Field Details

Understand common applications of CQL to be opportunistic!

• Smart Employee Groups
• Heatmaps: Create threshold-based buckets
• Eligibility Filters
• “Scores”
• Risk Flags
• Comp “Math” & Ratios
• Multipliers
• Comp History Fields
• Specific Equity Grants and Values

​

Interesting and practical usage of CQL

Popular uses of CQL in different areas of the platform

How to get started - The “Greatest Hits” of CQL

​

Popular usage in different areas of ChartHop

• Org Chart
• Custom Fields
• Data Sheet

Survey

What kind of data, information, or insights would be helpful to see on an org chart visualization?

​

What types of org health metrics and insight would you (or your business leaders) like to have access to daily that you/they do not right now?

​

​

How to get started - The “Greatest Hits” of CQL

​

Org Chart Filters

• View Multiple Cross-Functional Teams at Once
• View Team + Location subgroup
• Tenured Employees: tenure > 24
• Recent Hires: startDate >= '2020-12-31'New Hires
• DE&I: gender:f && directs > 0
• Myers Briggs

​

ChartHop Use Case Spotlight

Hiring Plans

New Hire Workflow

Survey!

What kind of data would you love to have in your hiring plan in ChartHop?

​

Building out a Hiring Plan in ChartHop

• Headcount Planning is one of the primary use cases of ChartHop and whether this is something that your company does annually, quarterly, or continuously, ChartHop provides a flexible interface that can accommodate any type of hiring plan.

​

• ChartHop’s extremely tight access control is one of the main benefits of using ChartHop to centralize your planning process. We can re-create the same column headers you previously used in excel or Google Sheets into our platform with the added benefit of setting fields to “Manager-Only” or “Highly Sensitive.”

• Using ChartHop managers can collaborate on Headcount Planning within a single datasheet view that compiles all of the relevant information.

​

Use Case Spotlight - Hiring Plans

How to Install our Headcount Planning Bundle

Create a saved view in the Datasheet

​

Use Case Spotlight - Hiring Plans

Capturing Data on New Hires in ChartHop

• Oftentimes, important data points like "Years of Experience at Hire" or "Referral Status" can live in the ATS and never make their way into your org data. Plus, typical HR systems don’t always make it easy to add in different custom field types and report on them over time.
• Down the line, if you want to pull reports on say, employee performance for referred employees vs non-referred employees, you’ll be able to do this.
• Build on the fields in this bundle add the entry of this data into your current workflows in your organization to set yourself up for long-term analytics success!

Use Case Spotlight - Hiring Plans

How to Install our New Hires Fields Bundle

Create a saved view in the Datasheet

​

Use Case Spotlight - Hiring Plans

Ideas!

Share any ideas you have for field additions to this bundle :)

​

Tools & Resources

• Webinar recording
• Webinar slide deck
• Community for questions, idea sharing, etc.
• Bundles (new!)
• Zendesk article (Log in or create an account here)

​

Next Steps

• Connect with CS for follow up questions on making the most of the bundle
• Continue engaging in the Slack community
• Provide us with feedback!
• Stay tuned for future sessions that build on more complex topics:
• Reports
• Bands & levels (using split(), .label and .num)
• Custom calculations for scenarios and comp planning (change.after.)

Closing & Next Steps