I'm trying to do an aggregation on a table that does not exist in the data model, but is dynamically created as a straight table in the GUI. In my data model, I have two tables, one giving the current stock of all articles, and one the expected transactions over the next month. From this, I compute the required orders in a straight table as:
[required orders] = rangemmax(0, -1*(Stock+[Goods receipt]-[Goods sold]))
The table might look like this (see also attached qvw):
| ArtID | Size | required orders |
|---|
001 | L | 5 |
| 001 | M | 0 |
| 002 | M | 10 |
| 002 | S | 0 |
Now I want an aggregated table, that just gives the sum of required orders per article ID, across all sizes:
| ArtID | required orders |
|---|
| 001 | 5 |
| 002 | 10 |
A simple pivot table won't work, because I only need to order an item if I predict the stock to go below zero. But if I aggregate, the current stock will also be aggregated before the substraction and addition of the transactions, and thus the result won't be correct. For example, if I have 100 size M items of article 001, then the aggregation won't tell me that I still need to order 5 items of this article because I'm running out of size L.
It would be easy if I computed the first table in the script, but I cannot do that, because the user needs to be able to dynamically pick a date for which the required orders should be computed (see qvw-file). Any ideas how to achieve this?
Thanks for your help!
