topic Re: Interval Between Two Dates in App Development

Interval Between Two Dates

kish87 — Thu, 04 Mar 2021 11:44:41 GMT

Dear Folks,

I need to display time difference b/w two dates with time stamps as shown below.

Am able to calculate TIME DIFF using Interval() as shown below

If(ACTIONBY=Above(ACTIONBY) AND Above(ACTIVITY)=’ Review’, Interval(ACT_DATE)-Above(ACT_DATE)))

I'm not including null's in below table to eliminate dates with nothing

TRANID	ACTIONBY	ACT_DATE	TIME DIFF
121500922	u1234	22-02-2021 11:01:27	00:04:20
121500922	u1234	01-03-2021 14:18:51	00:07:55

Now when I Add the other dimension ACTIVITY / ACTION i cannot see TIME DIFF value in the table

actual table looks like below

TRANID	ACTIONBY	ACTIVITY	ACTION	ACT_DATE
121500922	u1234	Review	Open	22-02-2021 11:01:27
121500922	u1234	Credit Review	Modify	22-02-2021 11:04:47
121500922	u1234	Review	Open	01-03-2021 14:18:51
121500922	u1234	Credit Review	Reject	01-03-2021 14:26:46

Can someone assist me on this?

Thanks In Advance.

Re: Interval Between Two Dates

Or — Thu, 04 Mar 2021 11:33:16 GMT

First, your sample data is a bit of a mess. You've got mixed date formats, an extra space in ACT_DATE for two values, and your ACTIONBY isn't the same user (uppercase U in the last one).

Assuming that's just a sample and not the actual data, though, it seems like within the context of your table ABOVE(Field) doesn't return anything, so you should be using Above(total Field):

If(ACTIONBY=Above(total ACTIONBY) AND Above(total ACTIVITY)= 'Review', Interval(ACT_DATE)-Above(total ACT_DATE))

Re: Interval Between Two Dates

kish87 — Thu, 04 Mar 2021 11:49:30 GMT

Thank You! Data remains Same but manual entry caused this mess so corrected and reposted it.

Re: Interval Between Two Dates

kish87 — Thu, 04 Mar 2021 12:17:55 GMT

Dear, Could you assist me with this to bring a derived field in backend please.

Thanks!

Re: Interval Between Two Dates

Or — Thu, 04 Mar 2021 12:34:52 GMT

I don't understand the question...

If you mean you want to pre-calculate it in the script, you'll need to do it with some sort of Peek() or Previous() situation (and you'll need to make sure your data is pre-sorted in the query / load). Have a look at those functions and see if you can get it to work, there's plenty of examples in Community for using those functions.