Can I create column names based on a formula?

[elsenorjose]

I am trying to create an IF formula that will give me column names based on a group of item numbers. Example:

if item in {1,2,3,4,5} then 'Basic'
else if item in {6,7,8,9} then 'Mid-Range'
else if item in {10,11,12} then 'Premium'
else item

However, I have another set of criteria:

if item in {1, 9, 12} then 'Black Label'

Some background: I am doing a project for a clothing company and they have a handful of items that fall into multiple categories, very much like what I describe above. The report we want would create summaries of the groups (Basic, Mid-Range, etc.) using those groups as column labels. Is this possible and secondly, if it is, how would I create the second grouping?

Using CR XI, IBM DB2 database on an AS/400. Thanks.

 

[synapsevampire]

I guess what you're asking is to create duplicate groupings for the same data, so that 1, 9, and 12 are also in another group?

There are a few ways you might approach this.

Try posting meaningful technical information, such as what the output might look like.

Successful posts tend to include:

Example data
Expected output

-k

 

[lbass]

I would try a manual crosstab for this. I don't know whether you would have any groups, but let's say you want to look at these categories (basic, mid-range, etc.) per store. Then you would insert a group on {table.store}. Then create four formulas, like:

//{@Basic}:
if {table.item} in 1 to 5 then {table.amt}

//{@MidRange}:
if {table.item} in 6 to 9 then {table.amt}

//{@Premium}:
if {table.item} in 10 to 12 then {table.amt}

//{@Black Label}:
if {table.item} in [1,9,12] then {table.amt}

Place these formulas in the detail section and then right click on each and insert a summary at the group level. Drag the groupname from the group header into the group footer.Then suppress the detail section and the group header.

-LB