Adding a year criteria to existing custom table calculation

Looker Q&A Looker
,
1

Good day all,

Been trying to find a way to add a year criteria to the first ‘if’ statement as per :

if week < 40

AND year = 2021

if(

${flxt_shipment_carrier_booking_ocean.first_port_latest_sched_departure_week_of_year} < 40  

OR position(${flxt_shipment_carrier_booking_ocean.carrier_booking_vessel_name},"BOMAR") = 1
OR position(${flxt_shipment_carrier_booking_ocean.carrier_booking_vessel_name},"MOLLY") = 1
OR position(${flxt_shipment_carrier_booking_ocean.carrier_booking_vessel_name},"HAMBURG EXPRESS") = 1
OR position(${flxt_shipment_carrier_booking_ocean.carrier_booking_vessel_name},"SANTA MARTA") = 1,

null,

if(

${conf_teus} < 20,
(20 - ${conf_teus})*-1,
null))

The above custom table calculation work but only if I don’t add the desired year criteria.

If I do add it, Looker doesn’t accept it saying “Arguments types for = must all match. Types given: (Date, Number)” for below:

if(${flxt_shipment_carrier_booking_ocean.first_port_latest_sched_departure_week_of_year} < 40 
  AND ${flxt_shipment_carrier_booking_ocean.first_port_latest_sched_departure_year} = 2021

      OR position(${flxt_shipment_carrier_booking_ocean.carrier_booking_vessel_name},"BOMAR") = 1
      OR position(${flxt_shipment_carrier_booking_ocean.carrier_booking_vessel_name},"MOLLY") = 1
      OR position(${flxt_shipment_carrier_booking_ocean.carrier_booking_vessel_name},"HAMBURG EXPRESS") = 1
      OR position(${flxt_shipment_carrier_booking_ocean.carrier_booking_vessel_name},"SANTA MARTA") = 1
      ,
null,

if(

${conf_teus} < 20,
(20 - ${conf_teus})*-1,
null))

Would be very grateful to anyone who can lend a helping hand.

Many thanks in advance.

2

@CYY it will be clearer to highlight or annotate the change in your code, it might seem difficult to compare both.

3

Hey @Kehinde ,

If you don’t read it then I agree it looks long.

The part that changes is the ‘if’ statement, as mentioned:

if(${flxt_shipment_carrier_booking_ocean.first_port_latest_sched_departure_week_of_year} < 40

vs

if(${flxt_shipment_carrier_booking_ocean.first_port_latest_sched_departure_week_of_year} < 40 
  AND ${flxt_shipment_carrier_booking_ocean.first_port_latest_sched_departure_year} = 2021

“Arguments types for = must all match. Types given: (Date, Number)”