Expand my Community achievements bar.

Applications for the Community Advisor Program Class of 2025 are NOW OPEN – Apply Today!

Adobe Customer Journey Analytics Finding difference between two metrics

Avatar

Level 1

Hi,

 

Consider a scenario where I want to get the occurrence count for events occurring between the difference of two metrics of the type datetime.

 

E.g. ingested records in aep contains two fields 'timestamp' and 'purchaseTimestamp'. I want to find the count of records for which the difference in 'timestamp' and 'purchaseTimestamp' lies between the category of '0-5 Min', '5-10Min', '10-15 min'.

 

Any suggestions, help is much appreciated

Topics

Topics help categorize Community content and increase your ability to discover relevant content.

4 Replies

Avatar

Level 2

You can achieve this using Adobe Experience Platform Query Service (SQL) by categorizing records based on the time difference between timestamp and purchaseTimestamp

Avatar

Level 1

Can it be achieved on CJA?

Avatar

Level 2

Create a calculated metric with below function

DATEDIFF("minute", purchaseTimestamp, timestamp)

This function calculates the difference between timestamp and purchaseTimestamp in minutes.

Avatar

Community Advisor

I don't believe there's currently a way to do to this in CJA.  The derived field functions that work with datetime fields are limited.  As @manojs57813206 mentioned, Query Service may be the best approach for now.

Alternatively, you could perform a full table export with both timestamps, but it'd probably be easier to use query service.