Explore custom calculations in Insights that everyone can use.
Average order value per sales rep
Use a table viz and select the following, for example:
Sales Rep. |
Number of orders |
Order value |
Jon Snow |
10 |
200 000 SEK |
Then, apply a "Table Calculation" as seen in the picture. Once in the table calculation window, you can simply start typing "Number..." and Looker will suggest the fields available in the table so you don't have to write out ${order.countorder} or anything scary like that.
- Simply start typing "sales..." of (it does not like ÅÄÖ). and select the field you want. It will show ${order.count_order}
- Complete your calculations like "sales value / number of orders"
- Select your format (%, €, $, #.## etc)
- Now hide your columns (if you want)
Note: the fields you want to use in your table calculation must be selected as well (you can hide these from the visualisation).
- And voilá!
Custom calculation for competitions etc
Lets say we want to create a dashboard as overview of who's booking the most meetings and/or closing the most activities.
- Select user, number of activities, and number of appointments
- Use a Table Calculation and name the first one Activity Points where every closed activity = 2 points
- Start typing "number of..." and select the field ${client_activity.count_activities} * 2
- Repeat step 2 but lets say booked appointments = 3 points
- Use "number of appointments" ${client_activity.count_aappointments} * 3
- Hide your other columns to only show who's leading the league in this contest
Change from last period (%)
(${client_activity.count_appointments}/offset(${client_activity.count_appointments},max(row())-row()))-1
IF NULL = 0 if(is_null(${antal_dagar}),0,${antal_dagar})
Show values instead of NULL
-- Scenario: Donald Duck has sold for 100SEK but has a budget of 0SEK, which makes his sales/quota 0%. The table calculation below uses an IF statement setting the value to 100% if the division yields NULL but shows the true value if it is not.
if(is_null(${0000_activity_quota_with_sales_se.order_value}/${0000_activity_quota_with_sales_se.quota_performed}),1,${0000_activity_quota_with_sales_se.order_value}/${0000_activity_quota_with_sales_se.quota_performed})
Calculation for Quota performed with values and not percent
Sometimes you want to create barcharts demonstrating quota performed with value and not percent, for each sales person as shown below where 149 000 is total quota, the green area is total sales and the grey area represents remaining to quota:
In this case when sales increases, the green sales part will increase and remaining to quota will decrease, but the total will remain fixed.
This seems to be quite easy but you need to create the following calculation to perform this:
IF(((Totala budgeten-Totala försäljningen)/Totala budgeten)*totala budgeten <0, 0, ((Totala budgeten-Totala försäljningen)/Totala budgeten)*Totala budgeten
In code we get:
if(((${sales_and_quotas.total_quota}-${sales_and_quotas.total_sales})/
${sales_and_quotas.total_quota})*${sales_and_quotas.total_quota}<0,0,
((${sales_and_quotas.total_quota}-${sales_and_quotas.total_sales})/
${sales_and_quotas.total_quota})*${sales_and_quotas.total_quota})