Functions, Formulas and Logical Operators

Arkieva allows users to add expressions to a variety of documents like Workbenches, Quick Reports, as well as define measures within the Arkieva Setup Manager. When defining the expressions, mathematical formulas and logical operators can be used to build up the envisioned expression.

Below list gives an overview of the different formulas and operators recognized by Arkieva and their respective parameters.

Conditional

• IF( condition, value_if_true, value_if_false, … )
• IN( item, set )
• SWITCH( condition1, value1, … )

Conversions

• INT( value )
• DECIMAL( value )
• FLOAT( value )
• TEXT( value )
• ISNULLZERO( value )

Date and Time

• DATE (year, month, day)
• DATEADD (interval, units, datetime)
• DATEDIFF ( interval, datetime, datetime)
• DATEONLY (year, month, day, hour, minute, second)
• DAY (datetime)
• DAYOFWEEK (datetime)
• DAYOFYEAR (datetime)
• DATEONLY (datetime)
• HOUR (datetime)
• MINUTE (datetime)
• MONTH (datetime)
• NOW()
• QUARTER (datetime)
• SECOND (datetime)
• TODAY()
• WEEK (datetime)
• YEAR (datetime)

Information

• GETUSERCULTURE()
• GETUSERID()

Logical

• AND( logical1, logical2 ) or logical1 && logical2
• NOT( logical )
• OR( logical1, logical2 ) or logical1 || logical2

Math

• ABS( number )
• MOD( number, divisor )
• ROUND( number, digits )
• TRUNC( number, digits )
• LOG( number)
• LOG10( number )
• EXP( number )

Operator

• [+](value + value)
• [-](value - value)
• [*](value * value)
• [/](value / value)
• [^](value ^ value)
• [>](value1 > value2)
• [>=](value1 >= value2)
• [\<](value1 \< value2)
• [\<=](value1 \<= value2)
• [\<>](value1 \<> value2)

Standard Aggregates

(Computed in query)

• AVERAGE ( aggregate )
• AVERAGEBYGROUP ( aggregate )
• COUNT ( aggregate )
• COUNTDISTINCT ( aggregate )
• MAX ( aggregate )
• MIN ( aggregate )
• RATIOTOREPORT ( aggregate )
• STDEV ( aggregate )
• STDEVP ( aggregate )
• SUM ( aggregate )
• VAR ( aggregate )
• VARP ( aggregate )
• WGHTAVG( aggregate, weightby ): SUM( weightby * aggregate ) / SUM( weightby )
• MPE( aggregate1, aggregate2 ): 100*( SUM( aggregate2 ) – SUM( aggregate1 ) / SUM( aggregate1 ) )
• MAPE( aggregate1, aggregate2 ): 100*( ABS( SUM( aggregate2 ) – SUM( aggregate1 ) ) / SUM( aggregate1 ) )
• WMAPE( aggregate1, aggregate2 ): 100 SUM( aggregate1 ABS( ( aggregate2 – aggregate1 ) / aggregate1 ) ) / SUM( aggregate1)
• UAPE( aggregate1, aggregate2 ): 100*( ABS( SUM( aggregate2 ) – SUM( aggregate1 ) ) / ( ( SUM( aggregate1 ) + SUM(aggregate2) ) /2 )
• GMRAE( aggregate1, aggregate2 ): EXP( LOG( ABS( SUM( aggregate2 ) – SUM( aggregate1 ) ) / SUM( aggregate1 ) ) / COUNT( 1 ) )
• BIAS( aggregate1, aggregate2 ): ( SUM( aggregate2 ) – SUM( aggregate1 ) ) / ( SUM( aggregate1 ) + SUM( aggregate2 ) )
• MAD( aggregate1, aggregate2 ): ( SUM( aggregate2 ) – SUM( aggregate1 ) ) / SUM( aggregate1 )
• Tracking Signal( aggregate1, aggregate2 ): ( ( SUM( aggregate2 ) – SUM( aggregate1 ) ) / SUM( aggregate1 ) ) / SUM( aggregate2 ) – SUM( aggregate1 )

Table-Valued Aggregates

• (Computed post query)
• CUMULATIVESUM( aggregate )
• CUMULATIVESUMBYPERIOD( aggregate, datetime, date interval)
• FIRST( aggregate )
• LAST( aggregate )
• MOVINGAVERAGE( aggregate , period )
• AVGDV( aggregate, +/-period )

Text

• CONCAT( string1, string2 ) or string1 & string2
• FIND ( string, substring ) ex. RIGHT([], (LENGTH([]) - FIND([], "%_%")) - 1)
• LEFT( string, length )
• LENGTH( string )
• LOWER( string )
• LTRIM( string )
• REPLACE( find, replace, string )
• RIGHT( string, length )
• RTRIM( string )
• SUBSTRING( string, start, length )
• UPPER( string )

Information Functions

Using the Information functions, you can get basic information about your users.

GetUserID

GetUser Culture