Visokio website     Downloads     Video tutorials     KnowledgeBase  
Formulae: Creating custom interval sizes? - Visokio Forums
Formulae: Creating custom interval sizes?
  • DBuzacott February 6, 2014 1:27AM

    We have a number of views that are split by value (decimal numbers). Currently we split this by a fixed interval It would be useful to be able to customise interval ranges to accommodate large value ranges. For example, we may want to split by value ranges such as 0-100, 100-1k, 1k-100k etcetera. FYI, in general we measure a sum (aggregation) of a cost field for a given category (i.e. number of categories with a total spend in a given range).

    We can do this a little with formula fields (IFs and SUBSETs), but this is limited when applying filters. For example, if we set up a calculation for total spend, this requires us to fix in the calculation the fields over which it is calculated. So we may set the calculation over subset([Site]), but then if we filter the view by another field (e.g. [Year]) then it doesn't update the calculation field accordingly.

    Ideally, we would be able to set this up in the view, rather than with a calculation field. The simplest method that I can think of is to allow custom interval ranges (i.e. not fixed-width) in the "Split" menu.
    Is this a possible addition?

    Thanks, Daniel
    Fixed intervals.png 12K
  •     paola February 6, 2014 4:36AM
    Can you please post a sample file so we can demonstrate the options.
  • DBuzacott February 6, 2014 7:38PM

    Thanks for the quick response!
    I've attached a file (with dummy data) with what we currently do and a bit of an explanation as to what we're trying to do. Let me know if you have questions.

  •        daniel February 7, 2014 4:33AM
    On a related note, would it be possible to have a look at Jenks natural breaks? It was a particularly useful way to automate varying sized interval breaks though used mainly in geographic data and often used in ArcGIS. Found it quite useful as an alternative to using quartiles/percentiles etc.

    Code example:

  •     paola February 7, 2014 7:19AM
    Please try using a different subset formula in
    [Sum of value by Year, Category]=

    This formula should give you flexibility to filter on all 3 fields.

    Please consider moving all the formulas to the DataManager workspace.
    This will give you another option to create buckets with custom intervals - data mapping. Using the Merge operation block you can merge your aggregated values with spreadsheet, that compares the sum in the [Value] field with [Min] and [Max] values (two merge criteria), in order to assign it to interval A, B, or C.
    See the image in this post:


It looks like you're new here. If you want to get involved, click one of these buttons!

Sign In Apply for Membership