Function
Description
AutoBinNumeric
Attempts to group the values in the specified column into the defined number of bins. However, there may be more or less bins than the defined number, because the method will always result in bins with limits that are easy to read (with as even numbers as possible).
The first argument is the column to bin and the second argument is the number of bins to aim for.
Example:
AutoBinNumeric([Column], 10)
BinByDateTime
Groups the values into bins based on a natural date, datetime or time hierarchy.
The first argument is the Date, Time or DateTime column to bin. The second argument is the definition of the levels in the hierarchy. The hierarchy levels should be written in the form of a string containing the desired date parts, separated by dots, for example "Year.Quarter.Month". The third argument is the pruning level which specifies the level of the hierarchy to display.
If you write a custom expression based on a BinByDateTime expression you will see a column selector with all levels of the specified hierarchy available, but with the hierarchy slider handle positioned at the specified pruning level.
Valid arguments for Arg2 are combinations of:
'year' or 'yy' - The year.
'quarter' or 'qq' - The quarter.
'month' or 'mm' - The month.
'day of year' or 'dy' - The day of year.
'day' or 'dd' - The day.
'week' or 'wk' - The week.
'day of week' or 'dw' - The weekday.
'hour' or 'hh' - The hour.
'minute' or 'mi' - The minute.
'second' or 'ss' - The second.
'millisecond' or 'ms' - The millisecond.
Example:
BinByDateTime([Column],"Year.Quarter.Month.Day",2)
For a date column called Order Date, the expression above would result in the column selector and hierarchy slider shown below when used in a custom expression:
Pruning level 0 would set the slider handle to the "year" position, 1 would mean the quarter, 2 the month, and 3 the day.
BinByEvenDistance
Groups the values in the specified column so there is an even distance between all bins.
The first argument is the column to bin and the second is the interval length. The third (optional) argument specifies where to start counting. If no start argument is specified, the first bin starts at zero.
Examples:
BinByEvenDistance([Column], 10)
BinByEvenDistance([Column], 10, 5)
BinByEvenDistribution
Groups the values in the specified column so that each bin (group) has the same number of unique values as the others. The last bin may have more unique values than the others.
The first argument is the column to bin and the second argument is the number of bins. Invalid values will give an invalid result.
Example:
BinByEvenDistribution([Column], 5)
BinByEvenDistribution(Rank([Column])*Count() + RowId(), 3)
BinByEvenIntervals
Groups the values in the specified column so that the value range is divided into equal intervals. The first argument is the column to bin and the second argument is the number of bins.
Example:
BinByEvenIntervals([Column], 5)
BinBySpecificLimits
Groups the values in the specified column by defined limits for the bins (groups). The first argument is the column to bin and the following arguments are the limits for the bins. All rows which have values larger than the largest limit will have the same bin value. Invalid values will give an invalid result.
Example:
BinBySpecificLimits([Column], 1, 2, 3, 10)
BinByStdDev
Groups the values into different bins depending on the values' distance from the mean, measured in standard deviations.
The first argument is the column to bin and the following arguments are the number of standard deviations to show from the mean. The standard deviation arguments should be given in ascending order and all values should be positive.
Example:
BinByStdDev([Column], 0.5, 1)
This will create a binning for:
≤ -1 standard deviation
-1 standard deviation
-0.5 standard deviation
0.5 standard deviation
1 standard deviation
> 1 standard deviation
BinBySubstring
Groups the values into bins based on the beginning or end characters of the value. This means that you can use this method to group all values beginning with "A" into one bin, "B" into another, etc.
The first argument is the string column to bin and the following is the number of characters in the substring. If the second argument is negative the substring starts from the end of the value.
Examples:
BinBySubstring([Column], 1)
BinBySubstring([Column], -4)
BinBySubstring(String([Integer Column]), 1)
BinByTimeSpan
Groups the values into bins based on a time span.
The first argument is the TimeSpan column to bin. The second argument is the hierarchy level definition written in the form of a string containing the desired time span parts, separated by dots (e.g., "Hours.Minutes"). The third argument is the zero-based pruning level which specifies the level of the hierarchy to display.
Valid arguments for Arg2 are combinations of:
'Days' - The days.
'Hours' - The hours.
'Minutes' - The minutes.
'Seconds' - The seconds.
'Milliseconds' - The milliseconds.
Example:
BinByTimeSpan([Column],"Days.Hours.Minutes.Seconds",2)
For a TimeSpan column called Time Difference, the expression above would result in the column selector and hierarchy slider shown below when used in a custom expression:
Pruning level 0 would set the slider handle to the "days" position, 1 would mean the hours, 2 the minutes, and 3 the seconds.
FiscalBinByDateTime
Groups the values into bins based on a natural date or datetime hierarchy, with the levels shifted the specified number of months.
The first argument is the Date or DateTime column to bin. The second argument is the hierarchy level definition written in the form of a string containing the desired date parts, separated by dots (e.g., "Year.Quarter.Month"). Only Year, Quarter and Month are supported. The third argument is the pruning level which specifies the level of the hierarchy to display. The fourth argument (optional) is the number of months to shift.
If no fourth argument is specified then the value of the document property FiscalYearOffset will be used.
Examples:
FiscalBinByDateTime([Date Column], "Year.Quarter.Month", 1, 2)
FiscalBinByDateTime([Date Column], "Year.Quarter.Month", 2)
