Max of columns in pivot table which are already calculated by BEFORE() function... Nesting BEFORE() and BELOW()?

[#1]

Hey all,

I have the following problem regarding a QlikView App where many values have to be calculated on the fly based on the user selection.

Suppose I have a shop with items A, B, C, D. Some amount is in the warehouse. Some of them are currently shipped to the warehouse.

And I have customers ordering the items.

Now I want to display a pivot table with customers and the items showing in the cells the amount they ordered.

The cells should get a background color based on whether the amount can be fully covered by the warehouse --> green, or by the items currently shipped to the warehouse --> yellow, or otherwise --> red.

The customers get sorted by their registration date and should be served according to the registration date.

i.e. first customer orders item A two times, second customer orderes item A one time. Two As are in the warehouse --> First customer gets the items from the warehouse, second customer and item A are displayed in red.

Now if the user only selects second customer, the cell is green. 

That is why I can not calculate any of the colors in the script. Everything is based on which customers the user selects. 

I managed to get that by using the BEFORE() function in the pivot table and calculate RangeSums.

See attached file.

Everything is working fine. 

What I now would like to have is an overall status for the customers:

(Every column should get an overall status)

Can the complete order be covered by warehouse etc.?

If at least one item of the customer order is red, then the customer would be displayed in red.

otherwise if at least one item is yellow, customer is yellow, otherwise green.

Any help or tips and tricks are highly appreciated. Thank you!

[#2]

Thank you so much for your help and the detailed answers!

It not only solved my problem, I also learned a lot about the Aggr()-function.