Time difference between two incidents

[no_hair]

Hi,

Suppose I have a time series data with 1 and 0 inputs. I’d like to calculate the time between two “1” incidents (let’s call it lasting effect, see red arrows in the screenshot), and show the lasting effect over time, or calculate the average lasting effect for a given category. Is there a good way to accomplish this?

Thank you!

[prantika_sinha]

Hi @no_hair ,

For this use case, I can think of using lag to fetch the previous timestamp and then do a date diff.
For the lag do consider sorting by the flag value field and then date-time field. that way you will be able to get the previous timestamp with value 1.

Let us know if this works out.

Thanks,
Prantika

[no_hair]

Hi Prantika,

Thank you for your reply! I was able to create the lag function.

When trying to create a calculated field for the date difference between the non-lagged timestamp and the lag timestamp, it gave me a mismatched aggregation error. I came across this post, and “aggregated” the non-lagged timestamp following Dylan’s suggestion, and it worked for my table.

"Lagged" and "current" versions of the same field cannot interact in a calculated field

Hello @nichbenn, welcome to the QuickSight community! I am curious, are you using this calculation in a table where the data is going to be displayed at the name/department level? The main issue with your function, is you are comparing an aggregated field and a nonaggregated field. If the data is being displayed at the correct partitions, you could aggregate the comparison hours by min, max, or sum, and it should then be able to be compared to the result of the lag function. Let me know if tha…

The next roadblock was when trying to get the average date difference for the large categories, I wasn’t able to create the calculation - “nested aggregation functions are not allowed”.

Is there somehow a way to get the Avg Diff numbers in the scenario below?

[Brett]

Hi @no_hair,
It’s been awhile since last communication on this thread. Did you have any additional questions or were you able to find a work around for your case?

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

Thank you!

[Brett]

Hi @no_hair,
Since we haven’t heard back, 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 and link this discussion for relevant information if needed.

Thank you!