Typical formula

Presentation

This page gathers some examples of formula with theirs explanations.

Allowed Comparison operators are : equal (=), greater than (>), lesser than (<)

Various formula for several usage

Flight hours done in one year for a given flight type

In following examples, replace 'flight type' by the flight type id.

If used in flight hours pricing formula or validity formula:

sumFlightTime(%PILOT, formatDate('yyyy',%NOW_DATE)-1, formatDate('M',%NOW_DATE), formatDate('d',%NOW_DATE), 0, 0, 0, 'flight type' )

If used in product sale formula:

sumFlightTime(%USER_ID, formatDate('yyyy',%NOW_DATE)-1, formatDate('M',%NOW_DATE), formatDate('d',%NOW_DATE), 0, 0, 0, 'flight type' )

Test age

Test age in flight hours pricing formula:

(getYearsFromDiffDate( getBirthdate(%PILOT), formatDate('yyyy-01-01',%NOW_DATE))>25)?1:0

Test age in product sale formula:

(getYearsFromDiffDate( getBirthdate(%USER_ID), formatDate('yyyy-01-01',%NOW_DATE))>25)?1:0

Activity sale engine (=flight hours pricing)

Apply another pricing after a period time

The pricing rule is as followed : If a flight starts after 7:00 am UTC (7:00 am included), we apply a pricing of $1. Otherwise, we apply a pricing of $2.

The formula will be :

( (formatDate('hmm',%START_DATE) > 659) ? $1 : $2 )

• %START_DATE is an internal variable representing the flight start date. The date is in UTC timezone
• formatDate is an internal function to format a date into another format
• formatDate('hmm',%START_DATE) will format the date by using the pattern hmm
  • It gives 1511 for the date 2011-01-05 15:11:01
  • It gives 700 for the date 2011-01-12 07:00:00
• (formatDate('hmm',%START_DATE) > 659) is the rule condition we want to apply
  • 659 represents 6:59 am
  • 659 is used instead of 700 because 7:00 am is included.
  • When comparing a time with this pattern 'hmm', the time range is 0 - 2359

The pricing rule is as followed : All flights are always done into France. If a flight starts after 11:00 am (11:00 am included), we apply a pricing of $1. Otherwise, we apply a pricing of $2. The formula will be :

( (formatDate( 'hmm',convertTimezone(%START_DATE, 'UTC','Europe/Paris') ) > 1100) ? $1 : $2 )

• convertTimezone is an internal function to convert a date from a timezone to another timezone
  • since %START_DATE is already in UTC, we let the timezone UTC as second parameter of the function
  • Europe/Paris is the timezone used in France
• Formula is regardless of daylight saving time

Apply another pricing after some flight hours within the current year

• The following digit "4" should be changed by the right flight type id.
• the following 600 number should be changed by the sexacentimal flight time step.

((sumFlightTime(%PILOT, formatDate('yyyy',%START_DATE), 01, 01, 00, 00, 0, 4 ) > 600) ? $tarifInstruction*%DURATION/600 : 0)

Apply a reduction when some flight hours are lower than a level

• The following digit "4" should be changed by the right flight type id.
• the following 600 number should be changed by the sexacentimal flight time step.

(sumFlightTime(%PILOT, formatDate('yyyy',%START_DATE), 01, 01, 00, 00, 0, 4 )<600)?($tarifInstruction*min(%DURATION,600-sumFlightTime(%PILOT, formatDate('yyyy',%START_DATE), 01, 01, 00, 00, 0, 4 ))/600):0

Apply a reduction when some flight hours are lower than a level on winter months

• Total instruction hours during winter period 1

@totalInstructionHours:

(formatDate('MM',%START_DATE)>10)?(sumFlightTime(%PILOT,formatDate('yyyy',%START_DATE),11,01,00,00,0,4)):0

• Total instruction hours during winter period 2

@totalInstructionHours:

(formatDate('MM',%START_DATE)<4)?(sumFlightTime(%PILOT,(formatDate('yyyy',%START_DATE)-1),11,01,00,00,0,4)):0

((@flightMonth>10) OR (@flightMonth<4))?((((@totalInstructionHours+%DURATION)/600)<5)?($tarifInstruction*%DURATION/600):($tarifInstruction*(5-(%DURATION/600)))):0

Quarter per hour billing with one hour minimum

The following formula bills a minimum of one hour then by size of quarter of hour.

max(4, roundCeil(%DURATION/150,1))*$price

Product sale engine

Apply a reduction according the bill date in the year

Example: apply 50% cut off if the bill date is the First of July or later in the year.

(formatDate('MM',%NOW_DATE)>6)?($summerRate/100*(@standardPrice)):0