Ranking with subquery
Ian Leigh May 13, 2016 11:19 AMI've been wrestling with this problem without success for some time. We have a data set of items to be sold which looks something like:
UniqueRef  ItemType  PreferredRank  OrderWeek 

FR3343  A  12  20160516 
FR3343  B  11  20160509 
KI4325  G  10  NULL 
PL4382  A  9  20160516 
FR3456  B  8  NULL 
FR7256  C  7  NULL 
PL8365  G  6  20160509 
PL2555  A  5  20160516 
MB8012  A  4  20160509 
MB8012  F  3  20160509 
MB8032  G  2  NULL 
PL8433  A  1  20160509 
Here there is a [PreferredRank] field, where a higher rank means we’d ideally like to sell it earlier. This is a unique field in the data which goes from 1 up to N (the number of items in the data).
The [OrderWeek] field shows if the item is present in an order which is due to ship in that particular week.
What I’m trying to achieve from this data is a summary table by order week that looks something like:
Week  No. Orders In Week  Cumulative Orders  Preferred Item Orders 

20160509  5  5  1 
20160516  3  8  1 
etc 
I can create the first two columns but it’s the third I’m struggling with. To explain what this column means:
To calculate the [Preferred Item Orders] by hand we’d carry out two steps in a calculation:
 Look at how many orders are present in total for the particular week (e.g there are 5 for week 20160509 in the above example).
 Check that many items in descending order of [PreferredRank] in the dataset, counting how many of them are due for sale in that same week.
For example, for the week 20160509 this would mean looking at the top 5 items (with [PreferredRank] = 12 down to 8) and counting how many had an [OrderWeek] = 20160509. In this case there is just the one.
In the case of the second week (20160516) this would mean instead of looking at the top 5 items by RankNo, we would instead look at the top 3 because that’s how many total orders there are in that week.
I've tried constructing an expression in many different ways but it’s this dynamic sub—query that’s giving me problems making one that works.
Am I overlooking something obvious here or is this perhaps something that’s not possible to achieve?
From reading elsewhere on this forum I understand that it may not be possible to use a dollar expansion over a dimension (as it’s just calculated once rather than per line as we would require here?) . Hence I'd be happy creating even multiple measures, one per week if that were necessary.
Hopefully I've managed to explain the problem sufficiently  I've also attached an example .qvf file with the same data.
Many Thanks.

Ranking Test.qvf 176.0 K