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 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.
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.
Please try using a different subset formula in [Sum of value by Year, Category]= SUBSET_SUM([Value],SUBSET3([Year],[Category],[Site]))
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: http://forums.visokio.com/discussion/comment/7974/#Comment_7974