Analytic functions listing
Overview
A function is a named calculation that uses input fields as arguments and returns a result. The result can be used in a custom formula, calculated member or other resource, or it can become an input to another function. Data Hub ships with a suite of ready-to-use system functions to assist with complex calculations.
Functions range from formulas for relative time, statistical functions and OLAP operations, and add powerful functionality to even the most basic cubes.
Functions use fields as input, and return a value, set, or member. Functions can be located in the Resource Explorer or from the right click menu on the columns and rows placeholders in an analyses, or from the functions place holder in a calculated member function.
A reduced functions list is shown when right clicking on a member placeholder in a report axis first, and then using the right click context menu. This filters the list to show functions only relevant to the selected member.
Logical
| Function | Sub category | Description |
|---|---|---|
| Contains Member Function | Returns True if the provided member can be found in the set. | |
| Is Current Member | Returns True if the current member in context matches the Member field. | |
| Is Empty | Returns true for formulas that evaluate to empty (null) data. However, this is not equivalent to comparing the value with null, because nulls are implicitly converted to zeros. | |
| Is Member Of | Returns True if the currently logged-on user is a member of the specified Active Directory or local group. | |
| Is User | Returns True if the currently logged-on user matches the user name field. |
Member
| Function | Sub category | Description |
|---|---|---|
| Ancestor | Returns the ancestor of a member at a specified level. | |
| Current Member | Returns the current member from a hierarchy. | |
| First | Returns the first member of set. | |
| Lag/Advance | Returns the member that is the specified number of positions prior to the specified member. | |
| Last | Returns the last member of set. | |
| Link Member | Returns a member parallel to the reference member- in the specified hierarchy. For example- returns the January Ship Date member for the the January Order Date member. | |
| Parallel Member | Returns a member from another period in the same relative position as the specified member. | |
| Parent | Returns the parent of the specified member. |
Parameterization
| Function | Sub category | Description |
|---|---|---|
| Checkbox | A parameterized checkbox entry that prompts the user when the resource is opened. | |
| Number Input | A parameterized numeric entry that prompts the user with a text box when the resource is opened. |
Set
| Function | Sub category | Description |
|---|---|---|
| Descendants | Set | Returns the descendants of the specified member. |
| Distinct | Set | Returns only the distinct members of a set. |
| Empty | Set | Returns a set composed of the empty members from the specified set. |
| Except | Set | Returns the difference between two specified sets. |
| Filter | Set | Returns the set resulting from filtering a set based on a condition. |
| Head | Set | Return a number of items from the start of a list. |
| Item | Set | Returns a specific item from a list. |
| Level by Index | Set | Returns a level specified by index from a specified hierarchy. The All level is 0. The Level Number function can be a useful complement to this function. |
| Non-Empty | Set | Returns a set of members from a set- which exist in a second set- where the members have non-null values. For example- returns the set of Products that have sales in New York. |
| Order | Set | Sorts the members of a specified set- optionally breaking the hierarchy. |
| Reverse | Set | Reverses the order of members in a set. |
| Static Set | Set | By default- sets are evaluated for each pivot table cell. Specifying a set as static evaluates it once- stores the result- and subsequently reuses the stored set. This may improve performance. |
| Sub Set | Set | Returns a sub-range of members from a set. |
| Tail | Set | Return a number of items from the end of a list. |
| Cross-Hierarchy Union | Set/Advanced | Occasionally it is necessary to union two sets from different hierarchies. This is best demonstrated with two time hierarchies; e.g. Created On and Re-opened On. A cross-hierarchy union of a set from each of these dimensions would result in all Created On or Re-opened On dates. |
| Exists | Set/Advanced | Returns items from a set, for which data exists in specified measure group. |
| For Each | Set/Advanced | For each member of the first set- an instance of the second set is generated. For example- for each of the top 5 sales regions- return the top 5 sales persons. |
| Include Cube Calculated Members | Set/Advanced | Adds cube calculated members into the current set. These are not included by default. |
| Intersect | Set/Advanced | Returns the intersection of two sets. For example- the intersection of January to July and June to December is June- July. |
| Names to Set | Set/Advanced | Returns the members from a given hierarchy that match on a comma separated list of names specified as text. |
| Conditional Set | Set/Dynamic Sets | Returns one of two specified sets- depending on whether the specified condition is True or False. |
| Contains | Set/Dynamic Sets | Returns a set of members- from the specified set- that contain the specified text. |
| Range | Set/Dynamic Sets | Returns a set of members between the specified ToMember and FromMember values - optionally including the members themselves. |
| Starts With | Set/Dynamic Sets | Returns a set of members- from the specified set- that begin with the specified text. |
| Bottom Count | Set/Top and Bottom | Returns a specified number of items from the bottom of a set. |
| Bottom Percent | Set/Top and Bottom | Returns a set with the lowest values- whose cumulative total is equal to or greater than a specified percentage. |
| Bottom Sum | Set/Top and Bottom | Returns a set with the lowest values whose sum is equal to or greater than a specified value. |
| Pareto (80-20) | Set/Top and Bottom | Returns a set with the items composing the top 80 percent and the sum of the items composing the bottom 20 percent. |
| Top Count | Set/Top and Bottom | Returns a specified number of items from the top of a set. |
| Top Percent | Set/Top and Bottom | Returns a set with the highest values- whose cumulative total is equal to or greater than a specified percentage. |
| Top Sum | Set/Top and Bottom | Returns a set with the highest values- whose sum is equal to or greater than a specified value. |
Text
| Function | Sub category | Description |
|---|---|---|
| Current Members | Returns the names of current members for the specified hierarchies; or- if no hierarchy is specified- the names of the current members for all non-default hierarchies. | |
| HTML | Returns specified HTML code. | |
| Join | Returns the members of a set, with the individual members are separated by semicolons. | |
| Member Name | Returns the name of a specified member. This function caters to conditional text. | |
| Text | Enables the entry of text into a formula. |
Value
| Function | Sub category | Description |
|---|---|---|
| Accumulate | Value | Returns a running total for the specified formula for the set on the opposite axis. |
| Aggregate | Value | Aggregate using the default operation defined for the hierarchy |
| Average | Value | Returns the arithmetic mean of the specified set. |
| Count | Value | Returns the count of the number of members in the specified set. |
| Count Existing | Value | Returns the count of items that have non-empty entries (exist) for the second set. For example- where Count returns the number of days in a month- CountExisting could return the number of days on which a sale occurred. |
| Delta | Value | Returns the difference between adjacent values of the specified formula for the set on the opposite axis. |
| Distinct Count | Value | Returns the distinct count of the number of members in the specified set. |
| Level Number | Value | Returns the level number for the specified member. The All level is 0. |
| Percent of Parent | Value | Returns the percentage of parent that the current member of a hierarchy forms- calculated for a specified formula. Optionally, restricts the calculation to only those parents on the opposite axis. |
| Rank | Value | Returns the rank of the specified formula for each item in the set on the opposite axis. NB ranking is expensive. It is therefore recommended that a Top or Bottom function be applied to the opposite axis to avoid ranking directly on large (thousands) sets. |
| Rank Set | Value | Returns the rank of the specified formula for each item in the specified set. NB ranking is expensive. It is therefore recommended that a Top or Bottom function first be applied to the rank set to avoid ranking directly on large (thousands) sets. |
| Sum | Value | Returns the sum of the specified set. |
| Absolute Value | Value/Math | Returns the value of a number without its sign. |
| Exponent | Value/Math | Returns e raised to the specified power. |
| Ln | Value/Math | Returns the natural logarithm (base e) of a number. |
| Log10 | Value/Math | Returns the base 10 logarithm of a specified number. |
| Power | Value/Math | Returns a specified number raised to the specified power. |
| Acos | Value/Math/Geospatial | Returns the angle whose cosine is the specified number. |
| Asin | Value/Math/Geospatial | Returns the angle whose sine is the specified number. |
| Atan | Value/Math/Geospatial | Returns the angle whose tangent is the specified number. |
| Atan2 | Value/Math/Geospatial | Returns the angle whose tangent is the quotient of two specified numbers. |
| Cos | Value/Math/Geospatial | Returns the cosine of the specified angle. |
| DegreesToRadians | Value/Math/Geospatial | Converts the specified angle in degrees to radians. |
| RadiansToDegrees | Value/Math/Geospatial | Converts the specified angle in radians to degrees. |
| Sin | Value/Math/Geospatial | Returns the sine of the specified angle. |
| Tan | Value/Math/Geospatial | Returns the tangent of the specified angle. |
| Ceiling | Value/Rounding | Returns the smallest integer value that is greater than or equal to the specified double-precision floating-point number. |
| Floor | Value/Rounding | Returns the largest integer less than or equal to the specified double-precision floating-point number. |
| Round | Value/Rounding | Rounds a double-precision floating-point value to a specified number of decimal digits. |
| Truncate | Value/Rounding | Calculates the integer part of a specified double-precision floating-point number. |
| Linear Regression | Value/Statistics | Returns the regression value, allowing you to show trend lines in charts. |
| Geometric Mean | Value/Statistics/Descriptive | Returns the geometric mean for the specified set. |
| Maximum | Value/Statistics/Descriptive | Returns the highest value from the specified set. |
| Median | Value/Statistics/Descriptive | Returns the median value (the middle value) in the specified set of ordered numbers. |
| Minimum | Value/Statistics/Descriptive | Returns the smallest value in a specified set. |
| Standard Deviation | Value/Statistics/Descriptive | Returns the standard deviation of a numeric expression evaluated over a set. |
| Value Range | Value/Statistics/Descriptive | Value Range is the length of the smallest interval which contains all the data. It is calculated by subtracting the smallest observation (sample minimum) from the greatest (sample maximum) and provides an indication of statistical dispersion. |
| Variance | Value/Statistics/Descriptive | Returns the variance of a specified numeric expression- evaluated over a specified set. |
Time
| Function | Sub category | Description |
|---|---|---|
| Closing Balance | Time | Returns the sum of the specified formula for all members up to the end of the specified time member. |
| Date Difference | Time | Returns the number of days, weeks or months between two dates. |
| Day of Week | Time | Returns the specified member's day of the week (Sunday through Saturday). |
| Days in Period | Time | Returns the number days in the current time member. |
| Last Complete Period | Time | Returns the last complete period for the provided time periods set. |
| Last Complete Periods | Time | Returns the last complete periods of data for the provided time period's set. |
| Most Recent Period | Time | Returns the most recent data member for the provided time periods set. |
| Most Recent Periods | Time | Returns the most recent periods of data for the provided time period's set. |
| Next | Time | Returns the next member in the level that contains the specified member. |
| Opening Balance | Time | Returns the sum of the specified formula for all members up to the end of the member preceding the specified time member. |
| Period on Period Growth | Time | Returns the growth from the previous period to the current in context. |
| Period to Date Members | Time | Returns the members to date for the specified period and level. |
| Periods Between | Time | Returns a set of all dates between the specified members including the members themselves. |
| Periods From | Time | Returns all members from and including the specified period. |
| Periods To | Time | Returns all members up to and including the specified period. |
| Previous | Time | Returns the previous member in the level that contains the specified member. |
| Previous Weekday | Time | Returns the previous weekday for the specified date member. |
| Rolling Sum | Time | Returns a rolling sum for the specified formula. |
| Context Period | Time/Current Period | Returns the period in context- either provided by the opposite axis or the filter context. |
| Current Date | Time/Current Period | Returns the current date. The date hierarchy is defaulted if only a single one exists. |
| Current Month | Time/Current Period | Returns the current month from the provided hierarchy. |
| Current Quarter | Time/Current Period | Returns the current quarter from the provided hierarchy. |
| Current Week | Time/Current Period | Returns the current week from the provided hierarchy. |
| Current Year | Time/Current Period | Returns the current year from the provided hierarchy. |
| Dates to Current | Time/Current Period | Returns all members up to and including the current date. |
| Months to Current | Time/Current Period | Returns all members up to and including the current month. |
| Quarters to Current | Time/Current Period | Returns all members up to and including the current quarter. |
| Weeks to Current | Time/Current Period | Returns all members up to and including the current week. |
| Years to Current | Time/Current Period | Returns all members up to and including the current year. |
| Last Complete Month | Time/Current Period/Last Complete | Returns the last complete month from the provided hierarchy. |
| Last Complete Quarter | Time/Current Period/Last Complete | Returns the last complete quarter from the provided hierarchy. |
| Last Complete Week | Time/Current Period/Last Complete | Returns the last complete week from the provided hierarchy. |
| Last Complete Year | Time/Current Period/Last Complete | Returns the last complete year from the provided hierarchy. |
| Most Recent Date | Time/Current Period/Most Recent Date | Returns the most recent date for which data exists, optionally evaluated over a second set. |
| Most Recent Month | Time/Current Period/Most Recent Date | Returns the most recent month for which data exists, optionally evaluated over a second set. |
| Most Recent Quarter | Time/Current Period/Most Recent Date | Returns the most recent quarter for which data exists, optionally evaluated over a second set. |
| Most Recent Week | Time/Current Period/Most Recent Date | Returns the most recent week for which data exists, optionally evaluated over a second set. |
| Most Recent Year | Time/Current Period/Most Recent Date | Returns the most recent year for which data exists, optionally evaluated over a second set. |
| Full Month (FM) | Time/Full Period | Returns a member representing the full month. |
| Full Quarter (FQ) | Time/Full Period | Returns a member representing the full quarter. |
| Full Week (FW) | Time/Full Period | Returns a member representing the full week. |
| Full Year (FY) | Time/Full Period | Returns a member representing the full year. |
| Exponential Moving Average | Time/Moving Averages | Returns an exponential moving average for the specified formula. |
| Simple Moving Average | Time/Moving Averages | Returns a simple moving average for the specified formula. |
| Weighted Moving Average | Time/Moving Averages | Returns a weighted moving average for the specified formula. |
| Month to Date Total (MTD) | /Time/Period to Date Totals | Returns the month to date total for the current time member. |
| Periods To Date Total (PTD) | /Time/Period to Date Totals | Returns the period to date total for a specified level. |
| Prior Month to Date Total | /Time/Period to Date Totals | Returns the month to date total for a member from the prior month. |
| Prior Quarter to Date Total | /Time/Period to Date Totals | Returns the quarter to date total for a member from the prior quarter. |
| Prior Week to Date Total | /Time/Period to Date Totals | Returns the week to date total for a member from the prior week. |
| Prior Year to Date Total | /Time/Period to Date Totals | Returns the year to date total for a member from the prior year. |
| Quarter to Date Total (QTD) | /Time/Period to Date Totals | Returns the quarter to date total for the current time member. |
| Total to Date (TTD) | /Time/Period to Date Totals | Returns the total-to-date amount for the current time member. |
| Week to Date Total (WTD) | /Time/Period to Date Totals | Returns the week-to-date total for the current time member. |
| Year to Date Total (YTD) | /Time/Period to Date Totals | Returns the year-to-date total for the current time member. |
| Last Year, Month to Date Total (LY MTD) | /Time/Period to Date Totals/Last Year | Returns the month-to-date total for the previous year's current time member. |
| Last Year, Quarter to Date Total (LY QTD) | /Time/Period to Date Totals/Last Year | Returns the quarter-to-date total for the previous year's current time member. |
| Last Year, Week to Date Total (LY WTD) | /Time/Period to Date Totals/Last Year | Returns the week-to-date total for the previous year's current time member. |
| Last Year, Year to Date Total (LY YTD) | /Time/Period to Date Totals/Last Year | Returns the year-to-date total for the previous year's current time member. |
| Parallel Period | /Time/Prior Period | Returns a member from another period in the same relative position as the specified member. |
| Period in Prior Month | /Time/Prior Period | Returns a member from the prior month- in the same relative position as the current time member. |
| Period in Prior Quarter | /Time/Prior Period | Returns a member from the prior quarter- in the same relative position as the current time member. |
| Period in Prior Week | /Time/Prior Period | Returns a member from the prior week- in the same relative position as the current time member. |
| Period in Prior Year | /Time/Prior Period | Returns a member from the prior year- in the same relative position as the current time member. |
Visualization
| Function | Sub category | Description |
|---|---|---|
| Sparkline | Creates small trend line charts (sparklines) within analyses based on a numeric formula and a specified time period. | |
| Status | Generates KPI status icons depending on the specified boundaries. |
Custom formula and custom set
| Function | Sub category | Description |
|---|---|---|
| Custom formula | Create a custom formula | |
| Custom Set | Returns True if the provided member can be found in the set. |
Reference member and reference set
| Function | Sub category | Description |
|---|---|---|
| Reference member | Reference another formula or member function. | |
| Reference set | Reference another set of members or a template set. |