I have two tables of data:-
An orderbook table, which contains data specific to a customer’s order e.g. quantity, material number, due date etc.
and, a shortage table, which contains data specific to the component(s) required to make an order.
I am trying to make a bar chart where X is current weeks dates i.e. 08/02/2021, 09/02/2021, 10/02/2021 etc.
Then Y is split into three categories: (1) Material Available [not stuck here] (2) components overdue [stuck here] and components will be late [and here].
I am trying to find a way to count if the max date of the components linked to the sales order is more than the delivery date given to the customer. I have tried this below but it reports an error; 'nested aggregation is not allowed'.
count(if(date(max([ZM06_Delivery/order finish date] > [STRIKE DATE], distinct [Sales Order]))))
where:
ZM06_Delivery/order finish date = supplier delivery date
STRIKE DATE = deliver date given to the customer
Sales Order = primary key
Does anyone know how I might solve this? - Thanks.
