Building Formulas with Functions
- Combine Data: Aggregate Functions
- Aggregations are used to perform mathematical calculations on data. You can run multiple
aggregations on several fields simultaneously, which makes it easy to summarize data
based on multiple factors.
- Syntax:
- Function (Numeric Field)
- Parameters:
-
Function:
Sum()
,Avg()
,Count()
,DupCount()
,Max()
,Min()
Numeric Field: A numeric field or formula.
For example, if you want to create a pivot table that shows the printer name and the average number of printed impressions, you can use a simple aggregation to calculate the average number of printed impressions for each printer:
AVG([Total Printed Impressions])
To also include the average number of printed impressions per day for each printer, you can use multi-pass aggregations that perform multiple calculations simultaneously. Instead of adding a new column for days in the pivot table, you can create a multi-pass aggregation that first calculates the sum of printed impressions per day and then averages the results for each printer:
AVG([Days in Date], [Total Printed Impressions])
- Summarize Data: Statistical Functions
- Statistical functions provide meaningful summaries of data and can help you make informed
decisions. They are especially useful for analyzing large data sets.
- Syntax:
- Function (Numeric Field)
- Parameters:
-
Function:
Median()
,Mode()
,Largest()
,Stdev()
,Stdevp()
,Varp()
,Var()
,Quartile()
,Percentile()
Numeric Field: A numeric field or formula.
For example, you can use statistical functions to understand typical results or make predictions based on central tendency and standard deviations.
- Accumulate Data: Running Total and Average
- Often, to measure performance, data must be viewed in a continuous and accumulative
format over extended periods such as years, quarters, or months. Several functions
are available for creating running totals and averages over standard or custom time
periods.
- Syntax:
- Function (Numeric Field)
- Parameters:
-
Function:
YTDAvg()
,QTDAvg()
,MTDAvg()
,YTDSum()
,QTDSum()
,MTDSum()
,RPSum()
,RPAvg()
Numeric Field: A numeric field or formula.
For example, you can use the Year to Date Average function to track progress towards increasing the number of printed jobs:
YTDAVG([# of unique Job ID])
- Change over Time: Time Functions
- Time functions let you compare results at different points in time and determine growth
rates. Functions can be set for standard time periods, such as year, quarter, month,
and day, or for custom periods.
Note: To calculate time functions, a time filter must be applied on the dashboard or widget.
- Syntax:
- Function (Numeric Field)
- Parameters:
-
Function:
PastYear()
,PastQuarter()
,PastMonth()
,Growth()
,GrowthRate()
,Contribution()
,YDiff()
,QDiff()
,MDiff()
,DDiff()
,HDiff()
,MnDiff()
,SDiff()
,Next()
,Prev()
,DiffPastYear()
,DiffPastQuarter()
,DiffPastMonth()
Numeric Field: A numeric field or formula.
For example, to compare growth in printed impressions to the same period in the previous year, you can use the DiffPastYear function:
DIFFPASTYEAR([Total Printed Impressions])