Formulae: Creating custom interval sizes?

DBuzacott · February 6, 2014 1:27AM

Hi,

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

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

Paola,

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

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.

http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#//00s50000001r000000

Code example:

http://www.mail-archive.com/r-sig-geo@stat.math.ethz.ch/msg00290.html

paola · February 7, 2014 7:19AM

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

Welcome!

Categories

Ideas Parade

Tagged