Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
so i have a table of policies and every policy can have multiple transactions in a table of transactions, i needed for every policy the latest transaction before a specific date DateCal and i used the following expression
= Aggr(Max({<ISSUEDATE={"<=$(=min(DateCal))"}>} IDTRANSACTION),IDPOLICY)
now i need the amount of the result transaction tried this:
=Sum({$<IDSECURITY={"$(=Max({<ISSUEDATE={"<=$(=min(DateCal))"}>} IDTRANSACTION))"}>} Amount)
but it calculates the IDTRANSACTION for a particular policy and does't give me just one policy in my table
how can i calculate this amount for the transaction found??
IDSECURITY={"$(=Max({<ISSUEDATE={"<=$(=min(DateCal))"}>
this will evaluate at a chart level
Can you post some sample data and expected output
it's actually {$<IDTRANSACTION={"$(=Max({<ISSUEDATE={"<=$(=min(DateCal))"}>} IDTRANSACTION))"}>} and yes because the data i want shown is dependant of the selected DateCal the expressions are on chart level in my case it's a table
an example of data
IDPOLICY IDTRANSACTION ISSUEDATE Amount
U3456 46789 20/12/2023 3000
U3456 44532 01/07/2023 290
U3456 34569 02/05/2023 600
So if I Chose the DateCal=02/01/2024,it should give me the latest transaction which is 46789 with the amount 3000
U3456 46789 20/12/2023 3000
if i chose for example the DateCal = 01/11/2023 It should give the latest transaction before the date :
U3456 44532 01/07/2023 290
yeah and IDPOLICY should be unique in this table
i hope this makes it clearer