Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi,
Please add max(ContractEndDate) instead of only ContractEndDate in the set expression.
thanks for your reply, but adding max as you suggested yields no results or inconsistent data.
Values returned and values expected are different