Hi everyone,
I need some help with the writing of a set analysis expression.
I have these two tables:
[Customers]:
LOAD
[IdCustomer],
[CustomerName],
[ContractEndDate];
LOAD * INLINE
[
IdCustomer;CustomerName;ContractEndDate
C1;BEN;20/03/2021
C2;ALICE;20/12/2021
](delimiter is ';');
[Sales]:
LOAD
[MasterDate],
[IdCustomer],
[TotalPrice];
LOAD * INLINE
[
MasterDate;IdCustomer;TotalPrice
25/01/2020;C1;10,90
07/02/2020;C1;15,00
28/02/2020;C2;19,70
15/03/2020;C1;15,50
05/01/2021;C1;10,00
05/02/2021;C1;15,00
10/02/2021;C2;20,00
15/06/2021;C2;14,60
20/08/2021;C1;10,40
10/10/2021;C1;12,50
12/12/2021;C2;11,50
](delimiter is ';');
I want to achieve this result in a sheet with a table:
Customer | Value
BEN | 25.00
ALICE | 46.10
where "value" is the sum of the TotalPrice between two dates: the end date is "ContractEndDate" from table customer and the start date is calculate from ContractEndDate minus 1 year.
I tried with this formula without any success:
sum( {<MasterDate={">=$(=Date(AddMonths(ContractEndDate,-12)+1))<=$(=ContractEndDate))"}>} TotalPrice)
Any suggestion on how can I write the correct expression?
Thank you
