Documentation for ProTrackWarehouse 2019.1.

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

Version 1 Current »


The following is a list of all the functions you can use in the formula editor.

Statistical Functions


FunctionUsageSyntaxExampleExample Result
AverageCalculates the mean average of the given values.AVG(<numeric field>)AVG(Score)Returns the mean average of the given scores.

Calculates the average of the given aggregation grouped by another field.AVG(<group-by field>, <aggregation>)AVG(Product, Total Sales)Returns the average of the total sales per product.
ContributionCalculates the percentage of total.CONTRIBUTION(<numeric field>)CONTRIBUTION(Total Sales)

Returns the percentage of total sales per group (for example per day or per product)

out of total sales (for all days or all products).

CorrelationReturns the correlation coefficient of two numeric fields.CORREL(<numeric field a>, <numeric field b>)CORREL(Revenue, Cost)Returns the correlation between revenue and cost.

Returns the correlation coefficient of two fields aggregations grouped by another field.CORREL(<group by field>, <aggregation a>, <aggregation b>)CORREL(Products, AVG(Revenue), AVG(Cost))Returns the correlation between the average of revenue and cost per product.
CountCounts the number of unique values within the given values.COUNT(<numeric field>)COUNT([Category ID])Returns the number of different category IDs within the given list of items.
Count AllReturns the actual item count of the given list of items, including duplicates.DUPCOUNT(<numeric field>)DUPCOUNT([Category ID])Returns the actual count of category IDs in the list of items.
Covariance (Population)Returns the population covariance of <Numeric Field a> and <Numeric Field b>.COVARP(<numeric field a>, <numeric field b>)COVARP(Revenue, Cost)Returns the population covariance of revenue and cost.

Returns the population covariance of two fields aggregations grouped by another field.COVARP(<group by field>, <aggregation a>, <aggregation b>)COVARP(Products, AVG(Revenue), AVG(Cost))

Returns the population covariance of the average revenue and

the average cost per product.

Covariance (Sample)Returns the sample covariance of <Numeric Field a> and <numeric field b>.COVAR(<numeric field a>, <numeric field b>)COVAR(Revenue, Cost)Returns the sample covariance of revenue and cost.

Returns the sample covariance of two fields aggregations

grouped by another field.

COVAR(<group by field>, <aggregation a>, <aggregation b>)COVAR(Products, AVG(Revenue), AVG(Cost))

Returns the sample covariance of the average revenue and the

average cost per product.

Exponential
Distribution

Returns the exponential distribution for a given value and a supplied

distribution parameter lambda.

EXPONDIST(<numeric field>, <lambda>, <Cumulative (true/false)>)EXPONDIST( Count(Leads), 2, False )

Returns the exponential distribution density of the number of leads

per country where lambda is 2.

Intercept

Returns the intercept of a linear regression line through the provided series
of x and y values.

INTERCEPT(<field>, <numeric field>)INTERCEPT(month.int, Total Sales)Returns the intercept of the regression line that represents the trend
of items sold for each month.
LargestReturns the k-th largest value in a field.LARGEST(<numeric field>, <k>)LARGEST(Total Sales,3)Returns the third-largest Total Sales value.
MaximumReturns the maximum value among the given values.MAX(<numeric field>)MAX(Total Revenue)Returns the item with the maximum Total Revenue.
MedianCalculates the median of the given values.MEDIAN(<numeric field>)MEDIAN(Total Revenue)Returns the item whose Total Revenue is the middlemost number in the set.
MinimumReturns the minimum value among the given values.MIN(<numeric field>)MIN(Total Revenue)Returns the item with the minimum Total Revenue.
ModeReturns the most frequently occurring value from the column.MODE(<numeric field>)MODE(Country ID)Returns return the country ID that is the most frequently occurring in the
list of items.
Normal DistributionReturns the standard normal distribution for a given value,
a supplied distribution mean and standard deviation.
NORMDIST(SUM(numeric field a),
<Mean (numeric field),All(numeric field)>,
<Standard Deviation (numeric field), All(numeric field)>,
<Cumulative (true/false)>)
NORMDIST(Score,
(Mean(Score), All(Score)),
(STDEV(Score), All(Score)),
False )
Returns the normal probability density of a given student score.
PercentileReturns the k-th percentile value from the given field.PERCENTILE(<numeric field>, <k>)PERCENTILE(Total Sales, 0.9)Returns the 90th percentile of Total Sales.
Poisson DistributionReturns the poisson distribution for a given value and a supplied distribution mean.POISSONDIST(<numeric field>, <mean>, <Cumulative (true/false)>)POISSONDIST(Score, Mean(Score), False)Returns the poisson probability density of a given number of scores.
QuartileReturns the k-th quartile for the given field. Can return
minimum value, first quartile, second quartile, third quartile, and max value.
QUARTILE(<numeric field>, <k>)
  • k = 0 returns the Minimum value
  • k = 1 returns the first quartile (25th percentile)
  • k = 2 returns the Median value (50th percentile)
  • k = 3 returns the third quartile (75th percentile)
  • k = 4 returns the Maximum value
QUARTILE(Score, 1)Returns the first quartile (25th percentile) of a given number of scores
RankReturns the rank of a value in a list of values

RANK(<numeric field>, [DESC/ASC], [Rank Type],
[<group by field 1>,... , <group by field n>])

RANK(Total Cost, "ASC", "1224",
Product, Years)
Returns the rank of the total annual cost per each product, sorted
in ascending order.
SkewnessReturns the skewness of the distribution of a given values.SKEW(<numeric field>)SKEW(Score)Returns the skewness of the distribution of scores.
SlopeReturns the slope of a linear regression line through the provided series of
x and y values.
SLOPE(<field>, <numeric field>)SLOPE(month.int, Total Sales)Returns the slope of the regression line that represents a trend of
items sold for each month.
Standard DeviationReturns the Standard Deviation of the given valuesSTDEV(<numeric field>)STDEV(score)Returns the Standard Deviation of the given values in the sample.
T DistributionReturns the T-distribution for a given value and a supplied number
of degrees of freedom.
TDIST( <numeric field x>,<degrees_freedom>,
<Cumulative (true/false)>
TDIST(Score, 3, TRUE)Returns the T-distribution of a given score, with 3 degrees of freedom.
VarianceReturns the Variance of the given values.VAR(<numeric field>)VAR(<Grade>)

Returns the variance of grades in a random sample

Mathematical Functions

FunctionUsageSyntaxExampleExample Result
AbsoluteReturns the absolute value of the given value.ABS(<numeric field>)ABS(Cost)Returns the absolute value of the cost.
AcosReturns the angle, in radians, whose cosine is the given numeric expression.ACOS(<numeric field>)ACOS(Total Revenue)Returns the arccosine of the given total revenue.
AsinReturns the angle, in radians, whose sine is the given numeric expression.ASIN(<numeric field>)ASIN(Total Revenue)Returns the arcsine of the given total revenue.
AtanReturns the angle, in radians, whose tangent is the given numeric expression.ATAN(<numeric field>)ATAN(Total Revenue)Returns the arctangent of the given total revenue.
CeilingReturns a number rounded up away from zero, to the nearest multiple of ‘1’.CEILING(<numeric field>)CEILING(Total Cost)Returns the total cost rounded up.
(ex. 83.2 will round up to 84)
CosReturns the trigonometric cosine of the given angle (in radians).COS(<numeric field>)COS(Average Angle)Returns the cosine of the average angle.
CoshReturns the hyperbolic cosine of the given value.COSH(<numeric field>)COSH(Total Revenue)Returns the hyperbolic cosine of the total revenue.
CotReturns the trigonometric cotangent of the given angle (in radians).COT(<numeric field>)COT(Average Angle)Return the trigonometric cotangent of the average angle.
ExpReturns the exponential value of the given value.EXP(<numeric field>)EXP(Sales)Return the exponential value of sales.
FloorReturns number rounded down, toward zero, to the nearest multiple of ‘1’.FLOOR(<numeric field>)FLOOR(Revenue)Returns the total cost rounded down.
(ex. 83.8 will round down to 83)
LnReturns the base-e logarithm of the given value.LN(<numeric field>)LN(Cost)Returns the natural log of the cost.
Log10Returns the base-10 logarithm of the given value.LOG10(<numeric field>)LOG10(Revenue)Returns the log of the revenue.
ModReturns the remainder after a number is divided by a divisor.MOD(<numeric field>, divisor)MOD(Cost, 10)Returns "Cost mod 10".
Ex. if cost is 52, returns 2.
PowerReturns the results of the given value raised to a supplied power.POWER(<numeric field., power)POWER(Revenue, 2)Returns revenue raised by the power of 2.
QuotientReturns the integer portion of a division.QUOTIENT(<numeric field>, divisor)QUOTIENT(Cost, 10)

Returns the integer division.
Ex. if cost is 52, returns 5.

RoundReturns number rounded to a specified number of digits.ROUND(<numeric field>, num_digits)ROUND(Revenue, 2)Returns the revenue rounded to two decimal places.
SinReturns the trigonometric sine of the given angle (in radians).SIN(<numeric field>)SIN(Average Angle)Returns the trigonometric sine of the average angle.
SinhReturns the hyperbolic sine of the given value.SINH(<numeric field>)SINH(Total Revenue)Returns the hyperbolic sine of the total revenue.
Square RootReturns the square root of the given value.SQRT(<numeric field>)SQRT(Cost)Returns the square root of the cost.
SumCalculates the total of the given valuesSUM(<numeric field>)SUM(Cost)Returns the total cost across all items.
TanReturns the trigonometric tangent of the given angle (in radians).TAN(<numeric field>)TAN(Average Angle)Returns the trigonometric sine of the average angle.
TanhReturns the hyperbolic tangent of the given value.TANH(<numeric field>)TANH(Total Revenue)Returns the hyperbolic tangent of the total revenue.

Time-Related Functions

FunctionUsageSyntaxExampleExample Result
Day DifferenceReturns the difference between <End Time> and <Start Time> in daysDDIFF(<End Time>, <Start Time>)DDIFF(Discharge Time, Admission Time)Returns the difference in days from the time of admission to hospital to the time of patient discharge.
Month DifferenceReturns the difference between <End Time> and <Start Time> in months.
Returns whole numbers.
MDIFF(<End Time>, <Start Time>)MDIFF(Arrival Time, Departure Time)Returns the difference in months from the time a ship departures from its departure port to the time of
arrival in its destination port. 
Quarter DifferenceReturns the difference between <End Time> and <Start Time> in quarters.
Returns whole numbers.
QDIFF(<End Time>, <Start Time>)QDIFF(End Semester, Start Semester)Returns the difference in quarters from the first academic semester to the graduation semester.
Year DifferenceReturns the difference between <End Time> and <Start Time> in years.
Returns whole numbers.
YDIFF(<End Time>, <Start Time>)YDIFF(Sentence End, Sentence Start)Returns the difference in years from sentence start to sentence end.
Second DifferenceReturns the difference between <End Time> and <Start Time> in seconds.SDIFF(<End Time>, <Start Time>)SDIFF(Leaving Time, Landing Time)Returns the difference in seconds from the time of landing on the page to the time of leaving the page.
Minute DifferenceReturns the difference between <End Time> and <Start Time> in minutes.MNDIFF(<End Time>, <Start Time>)MNDIFF(Payment Completed Time,
Landing Time)
Returns the difference in minutes from the time of landing on the page to the time of completing payment.
Hour DifferenceReturns the difference between <End Time> and <Start Time> in hours.
Returns whole numbers.
HDIFF(<End Time>, <Start Time>)HDIFF(Attendance Time, Check-in Time)Returns the difference in hours between the check-in time to the Emergency Room and time of attendance
by the doctor. 
Past Week DifferenceReturns the difference between this week's data and the data from the
previous week.

Use this function when the time resolution used in your widget is day or week.
DIFFPASTWEEK(<numeric field>)DIFFPASTWEEK(Total Sales)Returns the difference between this week's sales and previous week's sales, for the displayed time resolution.
For example, for day resolution: (sales in current day - sales in same day one week back).
For week resolution: (sales in current week - sales in previous week)
Past Month DifferenceReturns the difference between this month's data and the data from the
previous month.
DIFFPASTMONTH(<numeric field>)DIFFPASTMONTH(Total Sales)Returns the difference between this month's sales and previous month's sales, for the displayed time resolution.
Past Quarter DifferenceReturns the difference between this quarter's data and the data from the
previous quarter.
DIFFPASTQUARTER(<numeric field>)DIFFPASTQUARTER(Total Sales)Returns the difference between this quarter's sales and previous quarter's sales, for the displayed time resolution.
Past Year DifferenceReturns the difference between this year's data and the data from the
previous year.
DIFFPASTYEAR(<numeric field>)DIFFPASTYEAR(Total Sales)Returns the difference between this year's sales and previous year's sales, for the displayed time resolution.
Past Period DifferenceReturns the difference between this period's data and the data from the
previous period, using the current time resolution.
DIFFPASTPERIOD(<numeric field>)DIFFPASTPERIOD(Total Sales)Returns the difference between this period's sales and previous period's sales.
GrowthCalculates growth over time. (0% baseline)
Accepts any time resolution (day, week, etc.) in the widget.
GROWTH(<numeric field>)GROWTH(Total Quantity)If this month your Total Quantity is 12, and last month it was 10, your Growth for this month is 20% (0.2).
Calculation: (12 – 10) / 10 = 0.2

If this year your Total Quantity is 80, and last year it was 100, your Growth for this year is -20% ( -0.2).
Calculation: (80 – 100) / 100 = -0.2
Growth RateCalculates growth over time. (100% baseline)
Accepts any time resolution (day, week, etc.) in the widget.
GROWTHRATE(<numeric field>)GROWTHRATE(Total Quantity)If this month your Total Quantity is 12, and last month it was 10, your Growth for this month is 120% (1.2).
Calculation: 12/ 10 = 1.2

If this year your Total Quantity is 80, and last year it was 100, your Growth for this year is 80% (0.8).
Calculation: 80 / 100 = 0.8
Growth Past WeekCalculates the growth from the past week to the current week.
Use this function when the time resolution in your widget is weeks or days.
GROWTHPASTWEEK(<numeric field>)GROWTHPASTWEEK(Total Sales)Calculates the difference between this week's sales and previous week's sales, for the displayed time resolution.
Growth Past MonthCalculates the growth from the past month to the current month.
Use this function when the time resolution in your widget is month or day.
GROWTHPASTMONTH(<numeric field>)GROWTHPASTMONTH(Total Sales)Calculates the difference between this month's sales and previous month's sales, for the displayed time resolution.
Growth Past QuarterCalculates the growth from the past quarter to the current quarter.
Use this function when the time resolution in your widget is month or quarter.
GROWTHPASTQUARTER(<numeric field>)GROWTHPASTQUARTER(Total Sales)Calculates the difference between this quarter's sales and previous quarter's sales, for the displayed time resolution.
Growth Past YearCalculates the growth from the past year to the current year.GROWTHPASTYEAR(<numeric field>)GROWTHPASTYEAR(Total Sales)Calculates the difference between this year's sales and previous year's sales, for the displayed time resolution.
PrevReturns the Time period Member in <Time Field> which is N periods back
from the current member.
((<numeric field>), PREV(<Time Field>,
[<N>]))
([Total Quantity], PREV([Months in Date], 2))Returns the Total Quantity value for the month that occurred two months ago.
NextReturns the value for the time-period member in <Time Field> which is N
periods after the current member.
((<numeric field>), NEXT(<Time Field>,
[<N>]))
([Total Quantity],NEXT([Months in Date], 2))Returns the Total Quantity value for the month occurring two months ahead.
NowReturns the value for the current time period.((<numeric field>), NOW(<Time Field>))([Total Quantity],NOW([Months in Date]))This formula returns the Total Quantity value for the current month.
Past DayReturns the value for the previous day.PASTDAY(<numeric field>)PASTDAY(Total Sales)Returns the Total Sales value one day back.
Past WeekReturns the value for the same period in the previous week.PASTWEEK(<numeric field>)PASTWEEK(Total Sales)Returns the Total Sales value one week back for the displayed time resolution.
Past MonthReturns the value for the same period in the previous month.PASTMONTH(<numeric field>)PASTMONTH(Total Sales)Returns the Total Sales value one month back for the displayed time resolution.
Past QuarterReturns the value for the same period in the previous quarter.PASTQUARTER(<numeric field>)PASTQUARTER(Total Sales)Returns the Total Sales value one quarter back for the displayed time resolution.
Past YearReturns the value for the same period in the previous year. PASTYEAR(<numeric field>)PASTYEAR(Total Sales)Returns the Total Sales value one year back for the displayed time resolution.
RangeReturns an accumulated value for a data set where two members of the same
dimension and level define the minimum and maximum values of the range.
RANGE(<Start Date>, <End Date>)( [Total Quantity],
RANGE ( PREV ( [Days in Date],2),
current( [Days in Date] )))
Returns the Total Quantity value in the range between two days ago and today.
Week to Date AverageReturns the running average starting from the beginning of the week up
to the current day.
WTDAVG(<numeric field>)WTDAVG(Total Sales)Returns the running average of Total Sales starting from the beginning of the week up to the current day.
Week to Date SumReturns the running total starting from the beginning of the week up
to the current day or week.
WTDSUM(<numeric field>)WTDSUM(Total Sales)Returns the running total of Total Sales starting from the beginning of the week up to the current day.
Month to Date AverageReturns the running average starting from the beginning of the month up
to the current day.
MTDAVG(<numeric field>)MTDAVG(Total Sales)Returns the running average of Total Sales starting from the beginning of the month up to the current day.
Month to Date SumReturns the running total starting from the beginning of the month up
to the current day or week.
MTDSUM(<numeric field>)MTDSUM(Total Sales)Returns the running total of Total Sales starting from the beginning of the month up to the current day.
Quarter to Date AverageReturns the running average starting from the beginning of the quarter up
to the current day.
QTDAVG(<numeric field>)QTDAVG(Total Sales)Returns the running average of Total Sales starting from the beginning of the quarter up to the current day.
Quarter to Date SumReturns the running total starting from the beginning of the quarter up
to the current day or week.
QTDSUM(<numeric field>)QTDSUM(Total Sales)Returns the running total of Total Sales starting from the beginning of the quarter up to the current day.
Year to Date AverageReturns the running average starting from the beginning of the year up
to the current day.
YTDAVG(<numeric field>)YTDAVG(Total Sales)Returns the running average of Total Sales starting from the beginning of the year up to the current day.
Year to Date SumReturns the running total starting from the beginning of the year up
to the current day or week.
YTDSUM(<numeric field>)YTDSUM(Total Sales)Returns the running total of Total Sales starting from the beginning of the year up to the current day.

Other Functions

FunctionUsageSyntaxExampleExample Result
AllIgnores the scope set on the dimension.
This function can only work as a parameter inside another formula, and not by itself.
ALL(<numeric field>)SUM(ALL(Items))Returns the sum of all items, ignoring filters.
CaseReturns the result_expression of the first condition evaluated as true.
When no condition is true, else_expression is returned, if one is defined.
(WHEN <condition> THEN <result_expression>
[ELSE <result_expression>] END)
CASE
WHEN SUM(Sales) < 100 THEN 1
WHEN SUM(Sales) < 1000 THEN 2
ELSE 3
END
Returns '1' when Total Sales is less than 100,
Returns '2' when Total Sales is less than 1000,
Returns '3' in any other case
IfReturns numeric expression '1' when the condition is true,
and expression '2' when the condition is false.
IF (<condition>, <numeric expression 1>,
<numeric expression 2>)
IF(Count(Sales)>100,
Sum(Sales)*1.1,
sum(Sales))
If the number of unique values within the Sales values
is larger than 100, the function will return Total Sales x 1.1.
Otherwise, returns only the Total Sales
IsNullReturns true if the expression doesn't contain data (Null).ISNULL(<numeric field>)IF(ISNULL(Cost),
0, Cost)
If the Cost is null, returns 0.  Otherwise, returns the cost.
OrderingReturns the numeric order position of rows sorted into ascending or descending order, breaking ties with further arguments.
The expressions must be aggregated by applying the MIN/MAX functions.
ORDERING(<expression1>,<expression2>)ORDERING(MIN([Sales Person Name]),
MIN([Days in Transaction_Date]), Sum([Sales]))
Returns the sum of sales, ordered by sales person
descending, breaking ties with days in transaction date.
  • No labels