I'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 | 2016-05-16 |
| FR3343 | B | 11 | 2016-05-09 |
| KI4325 | G | 10 | NULL |
| PL4382 | A | 9 | 2016-05-16 |
| FR3456 | B | 8 | NULL |
| FR7256 | C | 7 | NULL |
| PL8365 | G | 6 | 2016-05-09 |
| PL2555 | A | 5 | 2016-05-16 |
| MB8012 | A | 4 | 2016-05-09 |
| MB8012 | F | 3 | 2016-05-09 |
| MB8032 | G | 2 | NULL |
| PL8433 | A | 1 | 2016-05-09 |
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 |
|---|
| 2016-05-09 | 5 | 5 | 1 |
| 2016-05-16 | 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 2016-05-09 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 2016-05-09 this would mean looking at the top 5 items (with [PreferredRank] = 12 down to 😎 and counting how many had an [OrderWeek] = 2016-05-09. In this case there is just the one.
In the case of the second week (2016-05-16) 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.
