Audit Trail Help.... Look for certain events in timeline

[fiorano]

HI,

I have an audit trail that resembles the following data :

ID	Timestamp	Details	Object/Action	Interaction
2764	12/04/2019 14:13:18	Open	App	Home
2764	12/04/2019 14:13:25	Navigate	View	Sales
2764	12/04/2019 14:13:30	Navigate	View	Data
2764	12/04/2019 14:13:39	Navigate	View	Leads
2764	12/04/2019 14:13:40	Search : UK	ListBox	UK_Search
2764	12/04/2019 14:21:46	Navigate	View	Leads
2764	12/04/2019 14:22:26	Table	Download	Customer_Table
2764	12/04/2019 14:25:26	Navigate	View	Items
2764	12/04/2019 14:25:39	Table	Download	Customer_Table
2764	12/04/2019 14:26:05	Back
2764	12/04/2019 14:26:06	Navigate	View	Data
2764	12/04/2019 14:26:08	Back	View
2764	12/04/2019 14:26:09	Navigate	Sheet	Items
2764	12/04/2019 14:38:19	Exit	App	Items

What i have been asked to do is check run through the table to see if the following pattern occurs:

Navigate to Leads view and check if the next chronological view is the Items view. If so, check if the Customer_Table is downloaded before the next Navigation event occurs.  I need to be able to count the number of instances where this happens.  

The identifier is the ID (which relates to each user session).

So, in the above dataset - I would have a count of 1 because at 14:21 the user Navigated to Leads page.  The next chronological page was the Items page and subsequently the Customer_Table was downloaded before the user navigated away to the Data page.

This is  properly giving me a headache so any help is greatly appreciated 

Yours Hopefully,

Fiorano

[MarqueeCrew]

@fiorano,

I used a Multi-Row formula to calculate the audit rule.  I grouped by the ID and used this expression:

IF 
	[Details] = "Navigate" AND [Interaction] = "Leads" AND
	[Row+1:Details] = "Table" AND [Row+1:Object/Action] = "Download"
THEN "True"
ELSE "False"
ENDIF

It seems to solve the challenge for me.

Cheers,

Mark