How to perform list calculations subtracting two dates

Need help Database
1

I have two dates stored on a User:

  1. Signed Up
  2. Completed Onboarding

I’m trying to calculate the min, max, and average times (in days) between these two dates for all users for an internally-used analytics dashboard.

I’ve been attempting to use the ListCalculator plugin, but it appears that it doesn’t support date math. (Or maybe I’m missing something.)

Are there any workarounds or plugins that would help me do this?
(I do not want to store the calculation as a field on the User itself – looking for ways to calculate this on-the-fly.)

2

Create a repeating group, make it data type of number, in that datasource set it to search for user then do each items signed up date - each items completed onboarding (or reverse that order) formatted as and choose days.

I’m not 100% sure that works as I am not testing the theory, but give it a test.

3

Try:
Search for Users:format as text

In the little window:
This User's Completed Onboarding - This User's Signed Up:format as days

Put a new line as the delimiter

Close out of the window and continue
:split by (new line):each item converted to number:min

Then copy paste the whole expression but change the last part to :average and :max

Just be aware this will basically load the entire User table in the browser so it could be WU intensive

4

@tylerboodman shared a nice and simple solution. It’s a good example to understand how some bubble functions work. If someone needs to write some javascript to do complex array calculations, they can use this solution.

By the way, he touched on a very important issue. If your user table is large, I suggest you use a satellite user table.

1 Like
5

Thanks all! @tylerboodman your solution worked perfectly.

1 Like