Preparing Bins of Values

[rushabh_shah]

Hi,

I have a numeric field which contains the revenue earned. I want to bucket this like 0-50,50-100,101-150 and so on.. I tried using Tile node but it gives out put as tile numbers.

I want the Range i.e. 0-50,50-100 to identify the buckets. Is there any possible way to do this?

Regards,

Rushabh

[MarqueeCrew · Accepted solution]

Is there a limit to your maximum value? It looks like you want to divide you input by 50 and determine the range that it belongs to. 

Say your value is 201. Divide it by 50 and you get 4.x. The range is 4x50 to 5x50 or 200 to 250. 

There is a floor() function and a ceil() function that you can use to calculate 4 and 5. 

I would then subtract 1 from the upper limit and make it 200 to 249. 

ToString(Floor([value]/50)*50) +  " to " +
ToString((Ceil([value]/50)*50)-1)

My iPhone code is untested, but should get you there. 

Cheers,

Mark

[Federica_FF]

HI,

you could create a small table with 2 columns:

Label | Tile Number

0-50 | 1

51-100 | 2

and so on...

and use a find and replace tool to find the Tile Number field and replace the values with the labels

or and if statement (probably without using the Tile Tool at all):

if [Revenue]<=50 then "0-50"
elseif [Revenue] <= 100 then "51-100"
elseif [Revenue] <=150 then "101-150"
else "151+"
endif

[Max06270]

---

@MarqueeCrew wrote:

Is there a limit to your maximum value? It looks like you want to divide you input by 50 and determine the range that it belongs to. 

Say your value is 201. Divide it by 50 and you get 4.x. The range is 4x50 to 5x50 or 200 to 250. 

There is a floor() function and a ceil() function that you can use to calculate 4 and 5. 

I would then subtract 1 from the upper limit and make it 200 to 249. 

ToString(Floor([value]/50)*50) +  " to " +
ToString((Ceil([value]/50)*50)-1)

My iPhone code is untested, but should get you there. 

Cheers,

Mark

---

Hey guys,

I was trying the achieve the same results and just want to say that this formula does not return expected results when value is a multiple of range. It gives the following:

value	value/50	floor(value/50)*50	ceil(value/50)*50-1	results	expected
0	0	0	-1	0 to -1	0 to 49
12.5	0.25	0	49	0 to 49	0 to 49
100	2	100	99	100 to 99	100 to 149

I don't know it this is the most elegant solution but since the struggle is with the upper side of the bucket and they all have the same size I am simply adding 50. Formula look like this then:

ToString(Floor([value]/50)*50) +  " to " + ToString(Floor([value]/50)*50+50-1)

and new results:

value	value/50	floor(value/50)*50	floor(value/50)*50+50-1	results	expected
0	0	0	49	0 to 49	0 to 49
12.5	0.25	0	49	0 to 49	0 to 49
100	2	100	149	100 to 149	100 to 149

Hope it is helpful.

Cheers,

Max

[derekbelyea]

HI

Here is an alternate solution making use of GENERATE ROWS to create standard interval buckets and the JOIN tool to sort the data into these buckets, following Mark's suggestion to use the FLOOR() function. 

Derek