I'm looking for a way to isolate the two highest values from a certain field, based on selections, and without using Set Analysis (Client is running 8.2). For example - if user selects 2009 Q2, 2009 Q1, 2008 Q4, 2008 Q3 - I need to isolate the values '2009 Q2' and '2009 Q1' (which may not be the last two values in the load order for this selection.
Since I may be going about the entire problem the wrong way - what I'm trying to do is create a table that looks like this:
Data (in reality, the data is not aggregated, each item/quarter will have multiple rows of data):
Item - ABCD Quarter - 2009 Q2 Sales - 12500
Item - ABCD Quarter - 2009 Q1 Sales - 5000
Item - ABCD Quarter - 2008 Q4 Sales - 15000
Item - ABCD Quarter - 2008 Q3 Sales - 7500
Table:
ITEM - AVERAGE SALES PER QUARTER - SALES LAST SELECTED QUARTER - SALES PREV. SELECTED QUARTER
ABCD - 10000 - 12500 - 5000
Any ideas on how to get this done efficiently (and ideally, without significant script modifications) would be appreciated. Keep in mind I can't pre-aggregate the values because I need them to change based on other selections (e.g. Customer, region, etc)
