Anyone, everyone, please help!!!!
I've been banging my head against everything in sight trying to figure this one out. I am trying to calculate the total and average daily log in times for users. The log in data is stored in an audit event table and the audit event timestamp contains all of the date/time info. Thus, the login and logout times are captured in the same column. I used the following formula to calculate the login time.
if Previous ({AUDIT_EVENT.EVENT_TYPE_ID})=100 and {AUDIT_EVENT.EVENT_TYPE_ID}=105
Then (DateDiff ('n',PREVIOUS({AUDIT_EVENT.EVENT_TIMESTAMP}),{AUDIT_EVENT.EVENT_TIMESTAMP})/60)
else if Previous ({AUDIT_EVENT.EVENT_TYPE_ID})=100 and {AUDIT_EVENT.EVENT_TYPE_ID}=103
Then (DateDiff ('n',PREVIOUS({AUDIT_EVENT.EVENT_TIMESTAMP}),{AUDIT_EVENT.EVENT_TIMESTAMP})/60)
The formula above works and displays the time in hours (e.g. 1.4).
I then try to create a running total field for this field and it will not allow me to do so. I then tried to use the "sum" formula and I get the "this field cannot be summarized" error message.
I've also tried exporting to a pivot table, but this too does not allow me to see the data in the format/level I need. It ends up entering an average for each login session which I don't need (need daily).
I'm thinking that I'm unable to sum this field because of the time when my formula is evaluating.
Any advice/help would be greatly appreciated!!!
Thanks in advance!!!
Nick