Partial aggregation in straight table

[#1]

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!

[sunny_talwar · Accepted solution]

This?

[#3]

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:

sum(if(Aggr(Stock + sum({$<Date={"<=$(vOrderDate)"}>} [Goods receipt]) - sum({$<Date={"<=$(vOrderDate)"}>}[Goods sold]), ArtID, Size)<0,

Aggr(Stock + sum({$<Date={"<=$(vOrderDate)"}>} [Goods receipt]) - sum({$<Date={"<=$(vOrderDate)"}>}[Goods sold]), ArtID, Size)))

I'm not sure how well this will perform with a lot of data, but at least it computes what I need. So thanks again!