Get average time difference between consecutive fields grouping by users

Q&A
,
1

I have data like field_id | user_id | statement | time . The data allows multiple statements per field_id melted in single rows.

I have created a field

“field per user greater than 1” = “ifelse({field per user} > 1, “Multiple fields”, {field per user} = 1, “One field”, “check”)” where

“field per user” = “distinctCountOver( {field_id}, [{user_id}], PRE_AGG )”.

I want to understand the average time difference between consecutive fields per user in the “Multiple fields category”.

Next i created “fieldTimestamp” = minOver({time}, [{field_id}, {user_id}], PRE_AGG)

“Previous field Timestamp” = lag({field Timestamp}, [{field Timestamp} ASC], 1, [{user_id}]

)

The datediff post this step is giving error: Mismatched aggregation. Custom aggregations can’t contain both aggregated and nonaggregated fields, in any combination.

Time Difference in Days = dateDiff(
{Previous field Timestamp},
{field Timestamp},
‘DD’
)

Can someone please help with a workaround?

If i use “fieldTimestamp” = min({time}) in place of minOver, it gives me the lags - but how do i show it in a single KPI?

  1. I want overall time difference between consecutive fields per user who have submitted multiple fields
  2. For the same data, i also want to check for how many users, the selected statements are the same more than once?