Question

Adding a year criteria to existing custom table calculation

  • 29 November 2021
  • 2 replies
  • 16 views

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.


This topic has been closed for comments

2 replies

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

 

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)”