If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Rank but filtered on 2 periods?

Started by thedon_1, 19 Dec 2011 09:17:50 AM

Previous topic - Next topic

thedon_1

i currently have the following:

Manufacturer    Rank     Profit   Value as % of total
A                       1           100      12%
B                       2           150     10%   

It's for one period so it's OK.

I need the same thing but for 2 periods. Currently, when I filter for 2 periods, It aggreagates the values and then does the ranking. I need a ranking for multiple periods done seperately.

Period   Manufacturer    Rank     Profit   Value as % of total
05          A                       1           100      12%
06          B                       1           150      10%   

I want it to look a bit more like the above where it will have a ranking for each period so for 2 periods, there will be 2 1's, 3 periods will have 3 1's etc.

The statement for my ranking is the following.

Rank([Profit] within set [Manufacturer])

I was thinking I could use the "for" function but it doesn't work.

blom0344

The standard strategy for creating sets that would either have different sources, calculations, filters etc would be defining unions.  I should allow you to do anything independant within a set as compared to any of the other sets (as long as number and datatypes of the results are alike.)
Would this solve the independant ranking?

 

thedon_1

One way I've seem it done is top use Rank([Profit] for [Manufacturer]).

This way, it uses the Manufacturer data and it looks like I showed above with a top 10 for each period.

Unfortuantely, it makes the % field calculate using the total of the top 10, not of everything for the period.

The expression in the Value as % of total field is [Profit]/total ([Profit] for [Period])