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, True()/False(), Range1, Range2, ...)

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

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 2017-10-06 16:59:57 UTC

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