If match / compare values then perform aggr sum function

[#1]

Hi all,

If the value of a certain column of a certain table matches that with the value of another column of another table, I want for those values an aggregated sum caried out;

It is possible that an OrderForecast number is not available in the Actual table and the amount of Order lines (both Forecast as well as Actual) may fluctuate.

But if the OrderForecast number is also present in the OrderActual column, I want to have the sum of the ForecastQuantity of all OrderForecast lines minus the sum of the ActualQuantity of all OrderActual lines.

Example output for OrderForecast 2 would be: (8+6) - (2+3+4+3)= 2

Thanks!

[Gysbert_Wassenaar · Accepted solution]

Rename the fields OrderForcast and OrderActual to OrderID to associate the two tables. Your expression would become sum(ForecastQuantity) - sum({<ForecastQuantity={'*'}>}ActualQuantity)