Multiple lookup conditions, including by date

Ask for Help
1

I am trying to calculate the quantity of units in certain mutual funds held on the last day of each reporting period (June 30). This is a cumulative total that may change from year to year. However, it is required only in relation to some and not all mutual funds in the table (ie those that have an ERI rate in the $ERI_rate field), so the cumulative tools in Grist do not appear to help. Hence, after filtering out funds that have no ERI rate, I would like to filter the quantities of each remaining fund by date.

I would like to do this in a field in the same table as that in which the quantities bought and sold are recorded so that the result can be used in other calculations. In other words, in [Table_Name].lookupRecords([A]=$[B]), the referenced table Table_Name is the same table as the referring table containing the field $[B].

I have come up with the following formula, but the date filter is not effective.

if $ERI_rate:
  quantities = general_Account.lookupRecords(Fund=$Fund).Quantity
  t = 0
  for q in quantities:
    if $Date <= datetime.date(int($ReportingPeriod),6,30):
      t += q
    else:
      t
  return t
else:
  return None

(The $ReportingPeriod field returns the relevant reporting year, eg 2024).

This produces exactly the same result as the following lookup with no date filter, namely the total held today, and not that held at the intermediate year-end dates:

if $ERI_rate:
  return sum(q.Quantity for q in general_Account.lookupRecords(Fund=$Fund))

As you will be able to tell, I am not very experienced at this and would be most appreciative of any help that could be given.

2

Solved my own problem (eventually):

A field for $ReportingPeriod

This field assumes that the Last Day of the Reporting Period is 30 June.
It returns the reporting period year-end in which the date of the transaction falls.

$date.year if $date.month < 7 else $date.year+1

A field for $LDRP

This field assumes that the Last Day of the Reporting Period is 30 June.
This field is restricted to ERI transactions, since otherwise the LDRP is irrelevant.
ERI is deemed received on 31 December, six months after LDRP. Hence, whilst the transaction occurs in a reporting period ending in the following year, the relevant LDRP is at the end of the previous reporting period.

datetime.date(int($ReportingPeriod)-1,6,30) if $type == "ERI" else None

The quantity of relevant units held on the LDRP

$Quantity_on_LDRP
This field is restricted to funds for which an ERI rate is given, since otherwise the quantity on the LDRP is irrelevant.

if $ERI_rate:
  return sum(q.quantity for q in general_Account.lookupRecords(Fund=$Fund) if q.date <= $LDRP)
1 Like