0

Creating custom age ranges in bar chart

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

7 comments

  • 0
    Avatar
    Janice Janczyn

    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

  • 0
    Avatar
    Kevin Burke

    Hi Janice,

    I was curious if you know of a way to have the age ranges sorted from lowest to highest?

    Because it's formatted as text , the sorting is not in the correct order 

    Thanks so much !

  • 0
    Avatar
    Janice Janczyn

    Hi Kevin,

    Yes, but first, to eliminate the decimal points in your x-axis, wrap the NUMBERFORMAT function around your each number manipulation result. By default this will set the result to use 0 decimal places. For example:

         CONCAT( NUMBERFORMAT( CEILING( data, 5)   - 4 ), "-", NUMBERFORMAT( CEILING( data, 5 ) ) )

    To sort numerically, add hidden data with basically the same formula as the x-axis, but formulated to return numeric results, so 41 instead of ">40" and 0 instead of "blank". The CEILING formula is simplified to return only 1 numeric value.

         IF( BETWEEN( data, 1, 40),
              CEILING( data, 5)
              IF( data > 40,
                   41,
                   0 )  )

    Then Sort this hidden data from lowest to highest and your klip will now be ordered correctly.

    Thanks,
             Janice

  • 0
    Avatar
    Kevin Burke

    Thanks ! The decimal point formula worked. 

    It seems the sort is still not working - here is my formula and even if I try formatting as Text or Number and trying Count or Sum aggregation they don't want to sort properly

  • 0
    Avatar
    Janice Janczyn

    Hi Kevin,

    Please enable support access and email support@klipfolio.com so we can take a closer look.

    Thank you,
                  Janice

  • 0
    Avatar
    Kevin Burke

    Support access enabled

     

    Thank you 

  • 0
    Avatar
    Janice Janczyn

    Hi Kevin, 

    I've created a support ticket for you. Please let us know which dashboard and klip to look at.

    Thank you,
                  Janice

Please sign in to leave a comment.