Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear Qlikers,
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
Value | 503,36 |
Reference AB | 118,58 |
Reference PJ | 36,30 |
Reference EB | 348,48 |
Thanks in advance, I hope you can help me!