Scroll

Custom Function: Group values by Range

This function takes a value and a number of ranges that you specify, and returns the range that the value falls within. This is useful for showing range frequencies for a set of data, to analyze trends and make histograms. Valid input types are numbers and DateTimes.

Note: Input ranges need to be in order from least to greatest.

Arguments: 3+

References:

  • System.Core.dll

Namespaces:

  • System.Linq

Usage

Range(Value, ShowFullRange, Endpoints...)

  • Value - the input value
  • ShowFullRange ( True() False() )
    • True() - show the full range, e.g. "100-200"
    • False() - show the top end of the range, e.g. "200"
  • Endpoints... ( Range1, Range2, ... - one or more range endpoints, in order from least to greatest

Example

=Range({InputField}, True(), 10, 20, 30, 40, 50)
// Results in output similar to the following:
InputField Range
33 30-40
26 20-30
60 50+

Code

DateTime dateVal;
double numVal;
bool isDate = DateTime.TryParse(args[0].ToString(), out dateVal);
bool isNum = double.TryParse(args[0].ToString(), out numVal);
bool isDisplay = bool.Parse(args[1].ToString());

if (isDate) {
    if (dateVal < DateTime.Parse(args[2].ToString()))
        return isDisplay
        	? "&#60; " + args[2]
        	: args[2];

    for (int i = 3; i < args.Length; i++) {
        if (dateVal > DateTime.Parse(args[i].ToString()))
            continue;
        else
            return isDisplay
            	? args[i - 1] + " &#45; " + args[i]
            	: args[i];
    }
    return isDisplay
        ? "&#62; " + args.Last()
        : args.Last();
}
if (isNum) {
    if (numVal < (double)args[2])
        return isDisplay
        	? "&#60; " + args[2]
        	: args[2];

    for (int i = 3; i < args.Length; i++) {
        if (numVal > (double)args[i])
            continue;
        else
            return isDisplay
            	? args[i - 1] + " &#45; " + args[i]
            	: args[i];
    }
    return isDisplay
        ? "&#62; " + args.Last()
        : args.Last();
}
return "Error: The first parameter must be a date or number";

Hidden Article Information

Article Author
Exago Development
created 2017-10-06 15:30:17 UTC
updated 2018-05-09 15:42:47 UTC

Labels
group, example, function, bucket, range,
Have more questions? Submit a request