Calculate difference between timestamps based on criteria

AppSheet AppSheet Q&A
1

Brand new to Appsheet.

I have a project tracker for different widgets installed at a project by several different installers. It includes a table that records a timestamp each time a widget is installed, what type of widget was installed, the installer and some other information. The columns in the table are: ID, Timestamp, Quantity Installed, Installer, Widget Installed.

I need to have a calculation between the timestamps between each time a widget is installed and to view that by installer.

Any help or suggestions would be appreciated!

2

If you haven’t already, it’s likely helpful to create tables for widgets and installers and reference those from your installations table.

Following are various techniques relevant to your use case.

Date and time expressions - AppSheet Help

How do I get a column value from the last row of this thing only?

Control grouping - AppSheet Help

REF_ROWS() - AppSheet Help

3

Hi @birdienumnum

  1. Create a virtual column in your table to calculate the time difference. Let’s call it “Time Difference.”

  2. In the “Time Difference” virtual column, use the following expression to calculate the time difference between each installation of a widget:

IF(
  COUNT(
    FILTER(
      "YourTable",
      AND(
        [Installer] = [_THISROW].[Installer],
        [Timestamp] < [_THISROW].[Timestamp]
      )
    )
  ) > 0,
  ([_THISROW].[Timestamp] - MAX(
    SELECT(
      YourTable[Timestamp],
      AND(
        [Installer] = [_THISROW].[Installer],
        [Timestamp] < [_THISROW].[Timestamp]
      )
    )
  )),
  ""
)

This expression checks if there are any previous installations by the same installer with an earlier timestamp. If such installations exist, it calculates the time difference between the current installation and the most recent previous installation. Otherwise, it leaves the cell blank.

  1. Save the virtual column, and it will automatically calculate the time difference for each widget installation.