Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Skip the ticket, Chat with Qlik Support instead for instant assistance.
cancel
Showing results for 
Search instead for 
Did you mean: 
valvesqlik
Contributor II
Contributor II

Set Analysis with other dimesion in expression

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

 

2 Replies
Rohan
Creator II
Creator II

Hi,

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

valvesqlik
Contributor II
Contributor II
Author

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

Values returned and values expected are different