Slide 23
Let’s look at some more examples of custom filters.
▪ We can combine multiple functions using the ‘AND’ and ‘OR’ operators.
If we want to see how many total TEU Hamburg has booked, but only dangerous
goods TEU that Southampton has booked, we could do this:
- Explore: Booking Container
- Dimension: Booking Office Code
- Dimension: Is Dangerous (Yes / No)
- Measure: Sum of TEU
- Filter: ${dms_bkg_master.bkg_ofc_cd} = "HAMBB"
OR ${dms_bkg_master.bkg_ofc_cd} = "SOUBB" AND
${dmc_cgo_ntr_cntr.is_dcgo} = yes
- Run
▪ If we want to see how many TEU have been booked to departure up to today but
excluding the last week, we could do this combining the functions add_days() and
now():
- Explore: Booking Container
- Measure: Sum of TEU
- Filter on Trunk POL ETD Date: ${dms_bkg_master.trnk_pol_etd_dt_date} <
add_days(-7,now())
- Note: Auto complete changes if a field isn’t included in our look.
- Visualisation: Single Value
Note: 24/03 -7 days is 17/03 (Don’t count today)
- Run
▪ Substring() is a useful function if you want to evaluate part of a field. Perhaps we
want to see all TEU that have been booked by branch offices, but there are too many
office codes to individually add to a filter:
- Explore: Booking Container
- Dimension: Booking Office Code
- Measure: Sum of TEU
- Filter: substring(${dms_bkg_master.bkg_ofc_cd}, 4, 2) = "BB"
Note: The numbers in the substring count the position to begin, and how
many characters to include.
- Run
We can see here that all the booking offices ending in BB are returned. We can swap this to
see third party agents too. – Change filter and re-run
▪ Another useful feature when filtering dates is the "matches (advanced)" function.
- Explore: Booking Container
- Dimension: Booking Create Date (Local)
- Measure: Count of Bookings
- Filter: Booking Create Date (Local) "matches (advanced)" 2020
- Run
We can see here that the filter is returning all the results for the year 2020.
We can also be slightly more vague providing what we are asking for makes logical sense.
- Filter: Booking Create Date (Local) - "matches (advanced)" = "Last Week"
- Run
- Filter: Booking Create Date (Local) - "matches (advanced)" = "3 Weeks ago"
- Run
- Filter: Booking Create Date (Local) - "matches (advanced)" = "Last Month"
- Run
Exercise answer slide 51
Let’s take a look at the solution.
<<DEMONSTRATION>>
▪ Explore: Booking Container
▪ Dimension: Booking Create Month (YYYY/MM)
▪ Measure: Sum of Gross Weight (MT)
▪ Table Calculation: running_total(${dms_bkg_cntr.sum_of_grs_wgt_mt}) (Name:
Running Weight Total | Format: 2 decimal places)
▪ Sort: Booking Create Month (Local) ascending
▪ Run
We also want to filter the data on booking create year of 2021, and hide the 'Sum of Gross
Weight (MT)' measure from our visualisation.
▪ Filter: Booking Create Year (Local) “is in the year” 2021 (this can be done using
"matches (advanced)" too)
▪ Hide: Sum of Gross Weight (MT)
▪ Visualisation: Column; Edit > Values > Value Format 0,, "M"
Does everyone have something that looks like this?
Please save your Look as Exercise 6 to your existing dashboard.
Slide 60
A common use for custom dimensions is creating new grouping or tiers.
In this example, the count of bookings is being grouped using a nested IF statement into
booking type with a default for data that doesn’t match the expressions’ criteria called
'Others'.
<<Talk through the expression>>
If we were to do the same with a table calculation, we would need to bring in the ‘Cargo
Type Name’ dimension which would cause the data to be granular to the Cargo Type Name
level. Remember, using a dimension returns all the unique values of that dimension,
providing supplementary information on each booking type, but it wouldn't be able to
redefine the grouping or change the level of data granularity. Only a custom dimension can
do that.
Another useful yet simple thing we can do with a custom dimension is a ‘Yes/No’
expression.
If for example we want to create a flag to see if cargo is refrigerated, we can enter an
expression based on ‘field equal to’ and it will return ‘Yes’ where there is a match or ‘No’ if
there is no match.
<<Demonstrate Expression>> ${dmc_cgo_ntr_cntr.cgo_ntr_nm} = "Reefer"
Slide 61
Custom measures allow us to make our own aggregations such as counts and sums. A
common use would be to count an item in the Explore that the LookML developers didn’t
think we’d need.
In this example, our custom measure is using a COUNT type to see how many BL Numbers
are in our data.
When creating a custom measure, Looker prompts us to pick a field first. Based on the field
we choose we get different options for measure type. For example, if we pick a string
dimension we would only be able to count or list its values. We wouldn’t be able to SUM or
AVERAGE words.
We can do a lot more with numerical dimensions such as SUM and AVERAGE.