Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
pvanderkolk
Partner - Contributor III
Partner - Contributor III

Last date value exceeds threshold

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.

PersonDateThresholdValue
David20-04-2019225957,11
David19-04-2019225957,11
David18-04-2019225503,36
David17-04-2019225503,36
David16-04-2019225503,36
David15-04-2019225503,36
David14-04-2019225154,88
David13-04-2019225154,88
David12-04-2019225118,58
David11-04-20192250
David10-04-20192250
David09-04-2019225200
David08-04-20192252015,75
David07-04-20192252015,75
David06-04-20192251885,37
David05-04-2019225812,82
David04-04-2019225812,82
David03-04-2019225812,82
David02-04-201922594,67
David01-04-20192250
David31-03-20192254871,05
David30-03-20192254871,05
David29-03-2019225270
David28-03-2019225150

 

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

Value503,36
Reference AB118,58
Reference PJ36,30
Reference EB348,48

Thanks in advance, I hope you can help me!

0 Replies