11 Replies Latest reply on Feb 12, 2013 3:42 PM by Jonathan Drummey

how to calculate for the difference in minute of datetime per row

Does anyone know how  to display the difference in minutes between each row of datetime? Column 1 shows the names of servers, column 2 with last updated time, and i would like the 3rd column to show  the difference in minutes. I'll adjust the visualization from there to show if the difference in greater than 3 minutes, color in red. Please help...

• 1. Re: how to calculate for the difference in minute of datetime per row

Hi Donna, I think you're looking for something like this:

ZN(ATTR(DATEPART('minute', [Date Time]))-

LOOKUP(ATTR(DATEPART('minute', [Date Time]) ),-1 ))

See Attached.

--Shawn

• 2. Re: how to calculate for the difference in minute of datetime per row

There's an error w/ the calculation. Please advise on syntax error per your reply- zn(attr(datepart('minute',[MAX_LASTUPDATEDPST])))-lookup(attr(datepart('minute',[MAX_LASTUPDATEDPST])),-1))) and if there is a difference in year, month, not just day and time, will the output provide that difference in minute or does the calculated field you provided only accounts for the differnece between minutes of the datetime?

• 3. Re: how to calculate for the difference in minute of datetime per row

What's the error?

And at this point the calculation only accounts for minute, not day or month or year. Give me a sample of real data in an Excel file and I can give you a better answer.

--Shawn

• 4. Re: how to calculate for the difference in minute of datetime per row

ZN(DATEDIFF('minute',LOOKUP(ATTR([Date Time]),-1 ),ATTR([Date Time])))

This will return the total minutes between dates and will work over month and years.

See attached.

--Shawn

2 of 2 people found this helpful
• 5. Re: how to calculate for the difference in minute of datetime per row

Thanks!!!

• 6. Re: how to calculate for the difference in minute of datetime per row

You're most welcome Donna.

--Shawn

• 7. Re: how to calculate for the difference in minute of datetime per row

Hi Shawn,

I sure hope you can help me out.  I've got the same problem, but somehow the calculation isn't working and I suspect that it has to do with partitioning or my date/time stamp sorting.  I'd sure appreciate some help - I've been wrestling with this for hours.

ZN(ATTR(DATEPART('minute', [CREATE TS]))-LOOKUP(ATTR(DATEPART('minute', [CREATE TS]) ),-1 ))

• 8. Re: how to calculate for the difference in minute of datetime per row

Pinging Jonathan

• 9. Re: how to calculate for the difference in minute of datetime per row

Hi Kelly,

Use DATEDIFF('minute',LOOKUP(ATTR([CREATE TS]),-1),ATTR([CREATE TS])) for the calc. DATEPART only gets the value given, so you were comparing the minute of the hour instead of actually subtracting time.

Jonathan

• 10. Re: how to calculate for the difference in minute of datetime per row

Thanks Jonathan,

Unfortunately it's still calculating on the CREATE TS for every ACTIVITY TYPE NAME and ACTIVITY STATUS.  It works perfectly if I remove those two, but I need them in the view.  Any suggestions?

Kelly

• 11. Re: how to calculate for the difference in minute of datetime per row

Can you provide a mockup of your desired results? I'm not sure what you want the calculation to be computing along and partitioned by.

Jonathan