Function Quick Reference Index

Function

Returns

Key Use Case

AddCalculatedMembers

Set

Add calc members to existing set

AllMembers

Set

All members including calculated

Ancestors

Set

Members above in hierarchy

Ascendants

Set

All members from member to root

BottomCount(Set, N, Measure)

Set

Lowest N members

BottomPercent(Set, %, Measure)

Set

Bottom N% of total

BottomSum(Set, Value, Measure)

Set

Bottom cumulative sum target

Children

Set

Direct child members

Crossjoin(Set1, Set2)

Set

Cartesian product

Descendants(Member, Level)

Set

All descendants at level

Distinct(Set)

Set

Deduplicated set

DrilldownLevel(Set)

Set

Expand to children

DrilldownLevelTop(Set, N)

Set

Expand top N children

DrilldownLevelBottom(Set, N)

Set

Expand bottom N children

DrilldownMember(Set1, Set2)

Set

Expand matching members

DrilldownMemberTop(Set1, Set2, N)

Set

Expand top N matching

DrilldownMemberBottom(Set1, Set2, N)

Set

Expand bottom N matching

Except(Set1, Set2)

Set

Set difference

Exists(Set1, Set2)

Set

Members with intersection

Extract(Set, Dim)

Set

Extract one dimension from tuples

Filter(Set, Condition)

Set

Members satisfying condition

Generate(Set, Expr)

Set/String

Apply per-member, union results

Head(Set, N)

Set

First N members

Hierarchize(Set)

Set

Hierarchy-ordered set

IIF(Cond, Set1, Set2)

Set

Conditional set

Intersect(Set1, Set2)

Set

Common members

Members

Set

All members at level/hierarchy

NonEmpty(Set)

Set

Non-null members

NonEmptyCrossjoin(Set1, Set2)

Set

Non-null cross product

Order(Set, Expr, Flag)

Set

Sorted set

PeriodsToDate(Level)

Set

From period start to current

Range (M1:M2)

Set

Contiguous member range

Siblings

Set

Same-level same-parent members

StripCalculatedMembers(Set)

Set

Remove calculated members

Subset(Set, Start, Count)

Set

Subset by position

Tail(Set, N)

Set

Last N members

TopCount(Set, N, Measure)

Set

Top N members

TopPercent(Set, %, Measure)

Set

Top N% of total

TopSum(Set, Value, Measure)

Set

Top cumulative sum target

Union(Set1, Set2)

Set

Combined unique members

Function

Returns

Key Use Case

SUM(Set, Measure)

Numeric

Sum over a set

AVG(Set, Measure)

Numeric

Average over a set

MIN(Set, Measure)

Numeric

Minimum value

MAX(Set, Measure)

Numeric

Maximum value

COUNT(Set)

Integer

Count members

Hierarchy.Levels.Count

Integer

Number of levels in hierarchy

Tuple.Count

Integer

Number of dimensions in a tuple

Member.Parent

Member

Immediate parent member

Hierarchy.Levels(N)

Level

Level by ordinal index

Hierarchy.Levels(Name)

Level

Level by name string

Members(Name_String)

Member

Resolve member from string

CASE...WHEN...END

Any

Multi-branch conditional

IIF(Cond, T, F)

Any

Inline conditional

Rank(Member, Set)

Integer

Rank within set

XIRR(Set, CF, Date)

Numeric

Internal rate of return

Median(Set, Measure)

Numeric

Median value

Percentile(Set, Measure, N)

Numeric

Nth percentile value

STDEVP(Set, Measure)

Numeric

Population std deviation

Format(Value, String)

String

Formatted value

POWER/POW(Base, Exp)

Numeric

Exponentiation

SQRT(Number)

Numeric

Square root

ROUND(Number, Precision)

Numeric

Round to N decimals

ROUNDUP(Number, Precision)

Numeric

Round up

ROUNDDOWN(Number, Precision)

Numeric

Round down

ABS(Number)

Numeric

Absolute value

LN(Number)

Numeric

Natural logarithm

EXP(Number)

Numeric

e^Number

Decile(Set, Measure)

Integer

Decile bucket (1-10)

CurrentOrdinal

Integer

Iteration index

AllValue(Measure, Dim)

Numeric

Grand total ignoring context

ROLLUPCHILDREN(Member, Op)

Numeric

Custom rollup

PROD(Set, Measure)

Numeric

Product of values

DIVIDE(N, D, Default)

Numeric

Safe division

ResponseSubset(Set, Cond)

Set

Kyvos filtered subset

ISUDA(Member)

Boolean

Is user-defined attribute

Function

Returns

Key Use Case

CurrentMember

Member

Current iteration member

Item(Index)

Member

Member at position in set

ParallelPeriod(Level, Lag)

Member

Prior period equivalent

StrToMember(String)

Member

Dynamic member from string

FirstChild

Member

First child member

LastChild

Member

Last child member

FirstSibling

Member

First sibling

LastSibling

Member

Last sibling

Ancestor(Member, Level)

Member

Parent at specified level

DefaultMember

Member

All/root member

Lag(N)

Member

N members back

Lead(N)

Member

N members forward

NextMember

Member

Next at same level

PreviousMember/PrevMember

Member

Previous at same level

FirstMember

Member

First at level

LastMember

Member

Last at level

IsLevel(Level)

Boolean

Is at specified level

Level.Name

String

Name of member's level

Level.UniqueName

String

Unique name of level

Member.Level

Level

Level object of member

LinkMember(Hierarchy)

Member

Equivalent in other hierarchy

Function

Returns

Key Use Case

IIF(Cond, T, F)

Any

Inline if-then-else

IS

Boolean

Member identity test

ISEMPTY(Value)

Boolean

Null cell test

AND

Boolean

Both conditions true

OR

Boolean

Either condition true

XOR

Boolean

Exactly one condition true

NOT

Boolean

Negate condition

IsAncestor(M1, M2)

Boolean

Ancestor relationship test

IsGeneration(M, N)

Boolean

Generation depth test

Function

Boundary

Key Use Case

YTD([Member])

Year

Year-to-date totals

MTD([Member])

Month

Month-to-date totals

QTD([Member])

Quarter

Quarter-to-date totals

WTD([Member])

Week

Week-to-date totals

Function

Returns

Key Use Case

DateAdd(Interval, N, Date)

Date

Add time to date

DateDiff(Interval, D1, D2)

Integer

Difference between dates

CDate(String)

Date

String to date conversion

Now()

DateTime

Current datetime

YEAR(Date)

Integer

Year component

MONTH(Date)

Integer

Month component (1-12)

GetDate()

Date

Kyvos: current date

GetTimestamp()

DateTime

Kyvos: current timestamp

Function

Returns

Key Use Case

Left(String, N)

String

Left N characters

Right(String, N)

String

Right N characters

InStr(String, Search)

Integer

Position of substring

Len(String)

Integer

String length

LTrim(String)

String

Remove leading spaces

RTrim(String)

String

Remove trailing spaces

CStr(Value)

String

Convert to string

Function

Returns

Key Use Case

Generation(Hierarchy, N)

Set

Members at generation N

NonemptyCount(Set)

Integer

Count non-empty cells

ClosingPeriod(Level, Member)

Member

Last period in parent

LastPeriods(N, Member)

Set

Last N periods

RelMemberRange(M, Start, End)

Set

Members in offset range

EXISTING Set

Set

Context-aware set

GetFilterInfo(Dim)

String

Active filter description

VisualTotals(Set)

Set

Subtotals for visible members

Function

Returns

Key Use Case

Identify_Member(Criteria)

Member

Find member by criteria

BITSETINTERSECTION(S1, S2)

Set

High-performance intersection

AllValue(Measure, Dim)

Numeric

Grand total ignoring filter

ResponseSubset(Set, Cond)

Set

Filtered result subset

GetDate()

Date

Current date

GetTimestamp()

DateTime

Current timestamp

ISUDA(Member)

Boolean

Is user-defined attribute