Hi All,
I have a requirement where i need to calculate the sum amount in an interval and the interval will be different for each id.
For example - i have below 2 tables in my data model-
| ID | Start date |
| 1 | 1/1/2019 |
| 2 | 1/10/2019 |
| 3 | 4/5/2019 |
| ID | Amount | Date |
| 1 | 10 | 2/2/2019 |
| 1 | 100 | 10/14/2019 |
| 2 | 110 | 3/2/2019 |
| 2 | 5 | 3/3/2020 |
| 2 | 15 | 4/3/2019 |
| 3 | 20 | 4/10/2019 |
| 3 | 30 | 4/13/2019 |
| 3 | 40 | 4/20/2019 |
So i need to find out the sum for each id where Date >= StartDate and Date <= AddMonths(Addmonths(StartDate),6).
Since we have different start date for each id so not able to expand the StartDate directly in where condition.
Expected output is
| ID | Amount |
| 1 | 10(10+0) |
| 2 | 125(110+15) |
| 3 | 90(20+30+40) |
For now I have used below expression with if condition -
Aggr(Sum(if(Date>= Min(StartDate) and Date <= AddMonths(Min(Date),6),Amount),ID)
Can you please suggest me some better solution. I need to do using set expression.
