Hi All ,
What should be set expression to achieve below result :
Source :
| Client |
target |
profit |
date_of_sale |
| C1 |
100 |
20 |
17/03/23 |
| C1 |
200 |
50 |
22/03/23 |
| C1 |
150 |
60 |
10/2/2023 |
| C2 |
50 |
10 |
12/3/2023 |
| C2 |
40 |
15 |
2/2/2023 |
| C3 |
30 |
5 |
1/2/2023 |
| C3 |
70 |
30 |
12/12/2022 |
output1
| Client |
target |
proft |
date_of_sale |
d1 |
d2 |
| C1 |
100 |
20 |
17/03/23 |
22/03/23 |
10/2/2023 |
| C1 |
200 |
50 |
22/03/23 |
22/03/23 |
10/2/2023 |
| C1 |
150 |
60 |
10/2/2023 |
22/03/23 |
10/2/2023 |
| C2 |
50 |
10 |
12/3/2023 |
12/3/2023 |
2/2/2023 |
| C2 |
40 |
15 |
2/2/2023 |
12/3/2023 |
2/2/2023 |
| C3 |
30 |
5 |
1/2/2023 |
1/2/2023 |
12/12/2022 |
| C3 |
70 |
30 |
12/12/2022 |
1/2/2023 |
12/12/2022 |
I tried this expression , which didn't work - Aggr(max(TOTAL <target,proft> date_of_sale),Client) for d1
I tried populating max of date from each client . Hence i tried ignoring dimension target,profit to get max of date aggregated over client .
Output2
| Client |
max_date |
min_date |
max_date_target |
min_date_target |
max_date_profit |
min_date_profit |
| C1 |
22/03/23 |
10/2/2023 |
200 |
150 |
50 |
60 |
| C2 |
12/3/2023 |
2/2/2023 |
50 |
40 |
10 |
15 |
| C3 |
1/2/2023 |
12/12/2022 |
30 |
70 |
5 |
30 |
Thank you All
