Calculation between records in a grid - Number of days between dates

Let's start with the basics;

• The data in your grid will be sourced from an array
• Ensure that the array is sorted in date order so that you can process the data in sequential order
• Process the data using a!forEach()
• You can do a date difference between the current item and the adjacent item (the latter using the current item's index offset by 1)
• note that there will be one item in your array that won't have an appropriate adjacent item so you'll need to make an exception for that item

This is similar to the idea I had, but when you say the adjacent item, are we doing like fv!index + 1 ? 

would you mind sharing an example of code? 

Something like this:

a!localVariables(
  local!mySortedArrayOfDates: {
    todate("09/10/2024"),
    todate("09/05/2024"),
    todate("09/01/2024")
  },

  a!forEach(
    items: local!mySortedArrayOfDates,
    expression: if(
      fv!isFirst,
      tointervalds(null),
      local!mySortedArrayOfDates[fv!index - 1] - fv!item
    )
  )
)

The result is an array of "intervals" in "Day to Second" format that will align with the array of dates you want to display.

Something like this:

a!localVariables(
  local!mySortedArrayOfDates: {
    todate("09/10/2024"),
    todate("09/05/2024"),
    todate("09/01/2024")
  },

  a!forEach(
    items: local!mySortedArrayOfDates,
    expression: if(
      fv!isFirst,
      tointervalds(null),
      local!mySortedArrayOfDates[fv!index - 1] - fv!item
    )
  )
)

The result is an array of "intervals" in "Day to Second" format that will align with the array of dates you want to display.

This part I understand, but I end up with an array of values, for the grid , I would need each row to show the correct value based on that displayed record.

fn!displayValue is your friend:

a!localVariables(
  local!mySortedArrayOfDates: {
    a!map(date: todate("09/10/2024")),
    a!map(date: todate("09/05/2024")),
    a!map(date: todate("09/01/2024"))
  },
  local!dates: fn!index(local!mySortedArrayOfDates, "date"),
  local!dateDifferences: a!forEach(
    items: local!dates,
    expression: if(
      fv!isFirst,
      "N/A",
      fixed((local!dates[fv!index - 1] - fv!item), 0)
    )
  ),
  {
    a!gridField(
      label: "Dates and date differences",
      data: local!mySortedArrayOfDates,
      columns: {
        a!gridColumn(value: fv!row.date),
        a!gridColumn(
          value: displayvalue(
            fv!row.date,
            local!mySortedArrayOfDates.date,
            local!dateDifferences,
            ""
          )
        )
      }
    )
  }
)

.

I haven't gone to the trouble to test it out but you should be able to retrieve your data as recordData and follow a similar approach to the above.

It's also occurred to me that you might want to sort the data in Primary Key/Identifier order as that should be the order the data was written to the table (in case there's a problem with the date data)