Isolate highest two values from a field

Or — Wed, 12 Aug 2009 20:44:37 GMT

Hello all,

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)

Thanks in advance 🙂

Isolate highest two values from a field

johnw — Thu, 13 Aug 2009 04:00:55 GMT

Here's one way:

sum(if(Quarter=max(total Quarter), Sales))
sum(if(Quarter=max(total Quarter,2), Sales))

See attached.

topic Isolate highest two values from a field in QlikView

Isolate highest two values from a field

Isolate highest two values from a field