Age Band Grouping

[crogers111]

CRXI
SQL

I'm using Ken H's forumla to calculate @Age and then I need to group into Age Bands which I'm doing with the following formula:

@AgeBands
If {@Age} >= 0 and {@Age} <= 1 then
"0 - 1"
else
If {@Age} >= 2 and {@Age} <= 6 then
"2 - 6"
else
If {@Age} >= 7 and {@Age} <= 18 then
"7 - 18"
else
If {@Age} >= 19 and {@Age} <= 22 then
"19 - 22"
else
If {@Age} >= 23 then
"23+"

The problem I'm having is that the order of the grouping is:

0-1
19 - 22
2 - 6
23+
7 - 8

I understand why this is happening (text) and am guessing I could correct this by making all the numbers 2 digits, but I don't really want to list ages bands as: 00 - 01, 02 - 06 if I can avoid it.

Is there a way to keep the age band numbering with single digits and still have the order be numerically correct like this:

0-1
2 - 6
7 - 8
19 - 22
23+

 

[lbass]

Use a formula that formats the numbers to two zeros for grouping, but then use a formula with the display you like instead of the groupname.

Another option is to use format->display string to display the formula the way you like.

-LB

 

[crogers111]

That did it.

I changed my @AgeBands formula to use 2 digits and then added a formula to the the format->display string as you suggested.

Thank you LB.