My issue is kind of tricky but I'll try to explain it as clearly as possible. I have an ID with an update date and a value.
I would like to create a fonctionnality which allows users to input a date and point to the corresponding sum of values corresponding to the update.
Here is an example of the data :
| ID | Value | Update date |
|---|---|---|
| 01 | 100 | 01/01/2019 |
| 01 | 200 | 15/10/2021 |
| 02 | 800 | 01/01/2019 |
| 03 | 400 | 16/10/2020 |
If the user inputs 20/10/2022 for instance, he should get the sum of rows 2,3 and 4. If the user inputs 10/01/2019, he should get the sum of rows 1 and 3.
Thanks in advance !
You don't say in which form you want this result. I am going to guess you want a single calculated value.
Try adding a Text area, then edit it and add a Calculated Value.
Given the small example provided, this worked. Set it up as below.
In the Data tab, type the Limit data using expression:
case when [Update date]<=Date('${myDate}') then [Update date]=Max([Update date]) over [ID] end
where ${myDate} is a document property you will have set up to contain your cutoff date.
In the Values tab select:
Sum(Value)
This should give you the sum of the maximum value of [Update date] for each ID, after filtering by the cutoff date.