IMOS - Sample Custom Expressions
Custom expressions are powerful; they enable you to create fields that do not already exist in the Veson IMOS Platform 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:
- Identifying Voyage Commence Month and Quarter
- Calculating Bunkers ROB on Departure from Previous Port
- Extracting Market Freight From Calculation Description
- Calculating Total Voyage Days
- Calculating Voyage P&L Results Based on Snapshots
- Calculating the Duration of a Date Range
- Calculating the Age Days for Invoices
- Calculating the Difference between Daily Costs
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 the same row is being checked 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 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.
Calculating Total Voyage Days
You can create a simple custom column that adds the Port Days and Sea Days columns together:
Expression: VoyageItineraries.PortDays+VoyageItineraries.SeaDays
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:
- Join the Voyage Pnl Table to the Voyage table.
- Filter on the new column Period Range Gmt.
- Enter the date range to which you want the calculation applied.
- 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:
- 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. - Add custom column Delta Cost, using this expression:
Daily Cost − Previous Cost