Breadcrumbs

IMOS - Sample Custom Expressions

Custom expressions are powerful; they enable you to create fields that do not already exist in the IMOS data fields.

Here are some sample custom expressions with functions you can use to create custom Report Designer columns, Task & Alert and Advanced Pricing Rule Values, and Data Dictionary Elements:

Note: Each expression assumes a certain base table in the report and, therefore, is not simply "plug and play."

Identifying Voyage Commence Month and Quarter

This expression uses nested IF statements to see if the numeric value of the month of the Commence Date of the voyage is within a list of numbers.

Base Tables: Voyage

Functions Used: IF, INLIST, TOLOCALESTRING, MONTH

Expression:

IF(INLIST(TOLOCALESTRING(MONTH(CommenceDateGmt)), "1"), "Jan", (IF(INLIST(TOLOCALESTRING(MONTH(CommenceDateGmt)), "2"), "Feb", (IF(INLIST(TOLOCALESTRING(MONTH(CommenceDateGmt)), "3"), "Mar", (IF(INLIST(TOLOCALESTRING(MONTH(CommenceDateGmt)), "4"), "Apr", (IF(INLIST(TOLOCALESTRING(MONTH(CommenceDateGmt)), "5"), "May", (IF(INLIST(TOLOCALESTRING(MONTH(CommenceDateGmt)), "6"), "Jun", (IF(INLIST(TOLOCALESTRING(MONTH(CommenceDateGmt)), "7"), "Jul", (IF(INLIST(TOLOCALESTRING(MONTH(CommenceDateGmt)), "8"), "Aug", (IF(INLIST(TOLOCALESTRING(MONTH(CommenceDateGmt)), "9"), "Sept", (IF(INLIST(TOLOCALESTRING(MONTH(CommenceDateGmt)), "10"), "Oct", (IF(INLIST(TOLOCALESTRING(MONTH(CommenceDateGmt)), "11"), "Nov","Dec")))))))))))))))))))))))

The Next Step

Apply the same logic with Quarters:

IF(INLIST(TOLOCALESTRING(MONTH(CommenceDateGmt)), "1", "2", "3"), "Q1", (IF(INLIST(TOLOCALESTRING(MONTH(CommenceDateGmt)), "4", "5", "6"), "Q2", (IF(INLIST(TOLOCALESTRING(MONTH(CommenceDateGmt)), "7", "8", "9"), "Q3","Q4")))))

Calculating Bunkers ROB on Departure from Previous Port

This expression uses ROWOFFSET to pull the Bunker ROB on departure from the previous port for display on the current row.

Base Tables: Voyage, Voyage Itinerary, Voyage Itinerary Bunker

Functions: ROWOFFSET, IF

Expression:

ROWOFFSET(-1, ORDERBY(VesselCode, VoyageNo, VoyageItineraries.VoyageItineraryBunkers.FuelType, VoyageItineraries.Seq)).VoyageItineraries.VoyageItineraryBunkers.RobDeparture

  • -1 indicates that the ROWOFFSET function will return the result of the row preceding the current row.

  • ORDERBY(...) tells ROWOFFSET that when determining the previous row, it should first order by Vessel Code, then Voyage Number, then Bunker Type, and finally Itinerary Sequence number.

  • .VoyageItineraries.VoyageItineraryBunkers.RobDeparture: The field name at the end tells the function which specific field to pull from the offset row.

The Next Step

Enhanced expression with vessel-matching check:

IF(ROWOFFSET(-1, ORDERBY(VesselCode, VoyageNo, VoyageItineraries.VoyageItineraryBunkers.FuelType, VoyageItineraries.Seq)).VesselCode == VesselCode, ROWOFFSET(-1, ORDERBY(VesselCode, VoyageNo, VoyageItineraries.VoyageItineraryBunkers.FuelType, VoyageItineraries.Seq)).VoyageItineraries.VoyageItineraryBunkers.RobDeparture, 0)

Extracting Market Freight From Calculation Description

This expression can be used to extract a Market Freight Rate $/MT that has been back-calculated from the $/day provided by Baltic. This requires the Trading module as well as the Market Data Feeds module.

Base Tables: Trade Contracts, Trade Pnl

Functions: SUBSTRING, IF

Expression:

SUBSTRING(SUBSTRING(TradeContracts.TradePnls.CalcDescription, 6), 0, IF(SUBSTRING(TradeContracts.TradePnls.CalcDescription, 7,1)==" ", 2, IF(SUBSTRING(TradeContracts.TradePnls.CalcDescription, 8,1)==" ", 3, IF(SUBSTRING(TradeContracts.TradePnls.CalcDescription, 9,1)==" ", 4, IF(SUBSTRING(TradeContracts.TradePnls.CalcDescription, 10,1)==" ", 5, IF(SUBSTRING(TradeContracts.TradePnls.CalcDescription, 11,1)==" ", 6, IF(SUBSTRING(TradeContracts.TradePnls.CalcDescription, 12,1)==" ", 7, IF(SUBSTRING(TradeContracts.TradePnls.CalcDescription, 13,1)==" ", 8, IF(SUBSTRING(TradeContracts.TradePnls.CalcDescription, 14,1)==" ", 9, IF(SUBSTRING(TradeContracts.TradePnls.CalcDescription, 15,1)==" ", 10, 0))))))))))

Calculating Total Voyage Days

Expression: VoyageItineraries.PortDays+VoyageItineraries.SeaDays

Calculating Voyage P&L Results Based on Snapshots

  1. Join the Voyage Pnl Table to the Voyage table.

  2. Filter on the new column Period Range Gmt.

  3. Enter the date range to which you want the calculation applied.

  4. To see just the relevant voyages, also apply a filter for Voyage Range GMT.

Calculating the Duration of a Date Range

TODAY() − DueDate

Calculating the Age Days for Invoices

DueDate == null ? 0 : (TODAY() - DueDate)

Calculating the Difference between Daily Costs

  1. Add custom column Previous Cost: ROWOFFSET(−1, ORDERBY (VesselCode,VoyageNo)).Daily Cost

  2. Add custom column Delta Cost: Daily Cost − Previous Cost

Extract specific text from a field/cell

Use SPLIT and SUBSTRING combined:

SUBSTRING(SPLIT(SUBSTRING(SPLIT(Form.FormErrors,"<Message>",1),0),"</Message>",0),0)