Set Analysis with other dimesion in expression

[valvesqlik]

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

[Rohan]

Hi,

Please add max(ContractEndDate) instead of only ContractEndDate in the set expression.

[valvesqlik]

thanks for your reply, but adding max as you suggested yields no results or inconsistent data.

Values returned and values expected are different