IMOS On-Prem - Sample Custom Expressions

The Custom Expression Editor is powerful; it enables 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 Cargo Matching programs:

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. If it is in that list, it then returns the name of the Month, rather than the number; otherwise, it moves on to the next condition. Note that the expression also skips the comparison for month 12; this comparison is unnecessary because the final result can only be December.

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

This example uses INLIST to perform the comparison because it makes it trivial to convert this same logic to apply Voyages to Quarters instead.

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. This may be useful in cases where you want to show Sea Leg Commence ROB and Sea Leg Complete ROB, as opposed to ROB on Arrival and ROB on Departure.

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

Breaking down the ROWOFFSET function, here is an explanation of each of the paramater values:

• -1 indicates that the ROWOFFSET function will return the result of the row preceding the current row.
• ORDERBY(VesselCode, VoyageNo, VoyageItineraries.VoyageItineraryBunkers.FuelType, VoyageItineraries.Seq) tells the ROWOFFSET function that when determining what is the previous row, it should first order the returned data by Vessel Code, then Voyage Number, then Bunker Type, and finally Itinerary Sequence number.

• .VoyageItineraries.VoyageItineraryBunkers.RobDeparture: The field name at the end of ROWOFFSET tells the function which specific field to pull from in the offset row. In this case, the function is pulling the previous row's Departure ROB.

The Next Step

You may notice that this function produces incorrect results when it encounters a previous row that is for a different vessel. This is because ROWOFFSET does not understand when different rows are no longer related to one another; it is a naive function. You can enhance this function by adding in new functions and making a more powerful expression.

First, you need to determine what makes a row related to another row. In this case, because the example is pulling bunker ROB, we care if the rows are for the same Vessel. To check if something is the same, we will need a conditional.

IF(Condition, True, False)

Next, we want to check if the previous row's Vessel is the same as the current row's Vessel. We already know that we can use ROWOFFSET to check the previous row's values, so we will just use that one more time. To finish our condition statement, we also need to compare the previous row to the current row, so we can use the field name. This is called the Condition Rowoffset.

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

Finally, we can complete our True and False results for the IF function. For True, we want to use the working function that returns the Departure ROB. This is called the Result Rowoffset. For False, we will return 0, because we don't know the previous ROB and can only assume a zero value. (The new lines are added to make the example more clear; you can add lines in your IF functions without causing any problems.)

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)

You will notice that ORDERBY is the same for both the Condition and the Result ROWOFFSET. This is necessary when using an IF in most scenarios because it is the only way to guarantee that IMOS is checking the same row in both the Condition Rowoffset and the Result Rowoffset. Though it seems long, wrapping ROWOFFSET functions in IF statements greatly improves their usability. It also provides the flexibility needed to define the relationship between the rows.

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

This function uses SUBSTRING to extract only the part of the Calculation Description that is relevant to the Freight Rate. The nested IF statements are used to determine where the first space character occurs in the Calculation Description and then returns that location as the Length of the first Substring. It does have a limitation in that it can only extract a freight in the format of ###.####, therefore assuming a maximum $/MT of $999.9999.

Adding Custom Font Colors

You can add custom font colors to a column:

In this example, the font color of the Opr Type column is set to green if the Operation Type is OVOV. For all other Operation Types, the font color is red:

Calculating Total Voyage Days

You can create a simple custom column that adds the Port Days and Sea Days columns together:

• Heading: Total Voyage Days
• Expression: VoyageItineraries.PortDays+VoyageItineraries.SeaDays
• Font Color: "Blue"
• Width: 125.00

Calculating Voyage P&L Results Based on Snapshots

You can calculate the P&L results from saved P&L Snapshots in the database, based on a period you enter and the saved calculation options:

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

You can calculate the difference between two dates as a number of days, using date and/or date-time values. For example:

TODAY() - DueDate

Calculating the Age Days for Invoices

You can calculate the age days for invoices using the alternative IF function. For example:

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

Calculating the Difference between Daily Costs

You can use a field in another row to calculate the difference between the previous Daily Cost and today's:

1. Add custom column Previous Cost, using this expression:
   ROWOFFSET(−1, ORDERBY (VesselCode,VoyageNo)).Daily Cost
   It takes the data from the Daily Cost field that is one row above, when the list is sorted by Vessel Code and Voyage No.
2. Add custom column Delta Cost, using this expression:
   Daily Cost − Previous Cost

Adding Red Highlight for Next Dry Dock within 30 Days

When creating a Cargo Matching program, you can format columns and add color-coded Alerts to columns to highlight various issues. This example shows how to highlight the Vessel Name in red when the next Dry Dock date is within 30 days.

In Design Mode:

1. See that Vessel Name is added as a column.
2. Click Vessel Name in the Columns pane.
3. In the Properties pane, enter the following in the Field Background field:
   
   IF(Vessel.VesselDetails.NextDryDock== null, "White", IF(TODAY()+30> Vessel.VesselDetails.NextDryDock, "Red", "White"))
   
   
4. The Next Dry Dock field is in the Vessel Details table; to add it:
   1. In the Voyage table, click  next to Vessel Code.
      (Hovering over shows [Voyage]Vessel Code.)
   2. In the Vessel Code table, click  next to Vessel Code.
      (Hovering over shows [Vessel]Vessel Code.)
   3. Select Next Dry Dock as a column.
      (Hovering over shows [Vessel Details]NextDryDock.)

If the Next Dry Dock date is less than 30 days away, the background for that vessel in the Vessel Name column turns red.