Hi ,
I've been searching up and down for any insight on how to go about this.
I have data containing ages in which I am trying to group in to custom age ranges. It would appear like the screenshot below. How do I go about this in building a klip ?
Thanks,
Kevin
Hi Kevin,
This type of grouping is simplest when each range covers the same number of ages because then you can use a single value to group most of the data using the CEILING function. For example, if you want your X Axis to have be groups of 5 year ranges, that is, 1-5,6-10, 11-15....36-40 as well as (blank) and >40, you could build it using something like the following:
IF( BETWEEN( data, 1, 40),
CONCAT( CEILING( data, 5) - 4, "-", CEILING( data, 5 ) ),
IF( data > 40, ">40", "(blank)" ) )
If your Series is a count of the number of items in each age range, use the following to
IF( BETWEEN( data, 1, 40 ),
CEILING( data, 5 ) ,
IF( data > 40, ">40", "(blank)" ) )
On the X Axis Properties tab, set the format to text and select Group repeating labels. Then on the Series Properties tab, set Aggregation to Count.
If you want your age ranges to be more variable, you will either have to build your formula to handle the special cases or pre-process the data so that it contains the age ranges you need.
I hope this helps!
Janice
