bottomSum

[kado]

Gurus,

I am trying to build a TOP 10 report from a relational package and am using topCount to get my Top 10 divisions. Unfortunately there are more than 10 and my users want to see everything else in an OTHER group. It sounds like bottomSum (just based on the name) is the way to go. But it is looking funky and I'm not sure if it's accurate. So basically 2 data items:

1) topCount([division], 10, [sales])
2) bottomSum([division], 11, [sales])

I am also confused by the definition IBM provides for this function, bottomSum:

Sorts "numeric_exp2", evaluated at the corresponding member of "set_exp", and picks up the bottommost elements whose cumulative total is equal to or less than "numeric_exp1".

Am I on the right track here? Any thoughts?

thanks,
kado

[kado]

I saw this post taking a RANK approach which could work (although the developer posting the question appears to not have had any luck):

http://www.cognoise.com/community/index.php?topic=5928.0

I would think something like bottomSum (or some Cognos function) could/should make it easier. Unfortunately the more I test bottomSum the odder the results.

???

THANKS,
kado

[MFGF]

Gurus,

I am trying to build a TOP 10 report from a relational package and am using topCount to get my Top 10 divisions. Unfortunately there are more than 10 and my users want to see everything else in an OTHER group. It sounds like bottomSum (just based on the name) is the way to go. But it is looking funky and I'm not sure if it's accurate. So basically 2 data items:

1) topCount([division], 10, [sales])
2) bottomSum([division], 11, [sales])

I am also confused by the definition IBM provides for this function, bottomSum:

Sorts "numeric_exp2", evaluated at the corresponding member of "set_exp", and picks up the bottommost elements whose cumulative total is equal to or less than "numeric_exp1".

Am I on the right track here? Any thoughts?

thanks,
kado

topCount(), bottomSum() etc are dimensional functions and are not designed to work with non-dimensional packages.  For a pure relational package, the way to go is:

1. Define a running-count calculation
2. Define a second calculation with the expression if ([running count calc] <= 10) then ('Best 10') else ('Others')
3. Group on the second calculation

Now define a boolean variable with the expression [your second calc] = 'Others'

Select the 'True' result of the boolean variable, select the list column bodies in your list, and set the box type property to be 'None'

This will then display the top 10, a summary of the top 10, and a summary of Others.

Regards,

MF.

[kado]

THANKs MF!!!!