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:
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 a lot! It's not quite what I need yet, because I can't simply take the min, in case more than one size of an article goes below 0, and I need to add those together. But you pointed me in the right direction, and I think I got it now, with an additional if statement:
