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.
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?
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])