Get average time difference between consecutive fields grouping by users

[priyanka2]

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?

[Brett]

Hi @priyanka2,

The issue seems to be arising from trying to incorporate an aggregated field with a pre-aggregated field as Quick Sight does not allow you to handle scenarios like these.
Here are a couple helpful articles that dive deeper into scenarios where mismatched aggregations arise:

Finding solutions for mismatched aggregation errors in Quick Sight calculations

Finding solutions for mismatched aggregation errors in Quick Sight calculations Mismatched aggregation errors are common Quick Sight calculation errors reported by users. This error occurs when the calculated field contains both aggregated and non-aggregated (row-level) values. For users new to Quick Sight, it’s not always apparent what causes this error. It is important to understand what is aggregated vs non-aggregated from a Quick Sight perspective. This article explores common cases where th…

Level-Aware Calculations: Tips and Best Practices

[Level-Aware Calculations in Amazon Quick Sight] Level-Aware Calculations (LAC) are powerful functions that allow us to perform calculations at a granularity level which is different from the granularity of our datasets and/or visuals. The sample dataset referenced by this article is a dataset of product orders placed by customers. Each order can contain multiple products and each product in an order has a unique Line Item ID. Each row of our dataset represents one line item but we want to pe…

I’d suggest to review these and see if it helps diagnose your issue; if you have any additional questions, it may be easier to assist further if you’re able to create an anonymized copy of your dataset and upload the analysis to Quick Sight Arena so that we can test out various work arounds.

[Brett]

Hi @priyanka2,

Following up here as it’s been awhile since last communication took place on this thread; did you have any additional questions regarding your initial post?

If we do not hear back within the next 3 business days, I’ll close out this topic.

Thank you

[Brett]

Hi @priyanka2,

Since we have not heard back further, I’ll go ahead and close out this topic. However, if you have any additional questions, feel free to create a new post in the community.

Thank you!