I have a value that can change every day. Like the title of the question already says, I'm looking for the last time that this value exceeded the threshold of 225. As you can see in the data below, this happened the last time on 15-04-2019 with a value of 503,36.
Person
Date
Threshold
Value
David
20-04-2019
225
957,11
David
19-04-2019
225
957,11
David
18-04-2019
225
503,36
David
17-04-2019
225
503,36
David
16-04-2019
225
503,36
David
15-04-2019
225
503,36
David
14-04-2019
225
154,88
David
13-04-2019
225
154,88
David
12-04-2019
225
118,58
David
11-04-2019
225
0
David
10-04-2019
225
0
David
09-04-2019
225
200
David
08-04-2019
225
2015,75
David
07-04-2019
225
2015,75
David
06-04-2019
225
1885,37
David
05-04-2019
225
812,82
David
04-04-2019
225
812,82
David
03-04-2019
225
812,82
David
02-04-2019
225
94,67
David
01-04-2019
225
0
David
31-03-2019
225
4871,05
David
30-03-2019
225
4871,05
David
29-03-2019
225
270
David
28-03-2019
225
150
I would like to present this value in a text box on my dashboard.
Further, I use the following script to calculate the field 'Value'.
Table: NoConcatenate LOAD *, If(Person=Previous(Person),RangeSum(IndAmount,Peek(Value)),IndAmount) as Value, Resident TempTable Order by Person, Date;
Next to the visualisation of the value on 15-04-2019 in the text box, I would like to show the field 'IndAmount'. This field contains the following amounts: 118,58; 36,30 (=154,88-118,58); 348,48 (=503,36-154,88). These amounts have a unique reference, which should be presented together with the amount (resp. AB; PJ; EB). These reference numbers are loaded from the database, they're not in the same table as field 'Value' (yet).
In the script it is defined as the accumulation of amount that leads to the field 'Value'. So the result should look like
