Binning functions

Binning is a way to group a number of more or less continuous values into a smaller number of "bins".


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. Alternatively, Arg2 can also be a combination of: '`isoyear`' - The ISO 8601 week-numbering year. '`isoweek`' - The ISO 8601 week number. Examples: `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. `BinByDateTime([Column],"ISOYear.ISOWeek",1)` To use binning according to ISO 8601 for a date column, use the `isoyear` and `isoweek` date parts rather than the regular `year` and `week` date parts.
`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:
`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)`

Note: If an axis using a custom expression with binning has been set up to Evaluate axis expression on: Current filtering only (applicable using the Windows client only), then the binning will be recalculated with each filtering.

Example

Consider a column with the following values:

If the column is binned using the binning methods below, the resulting limits are as follows:


Binning method	Result
`AutoBinNumeric([Column],5)`
`BinByEvenDistance([Column],5)`
`BinByEvenDistribution([Column],5)`
`BinByEvenIntervals([Column],5)`
`BinBySpecificLimits([Column],-2,4,10,16,22)`

Tip: You can change the formatting of the column to view fewer (or no) decimals on the axis.