Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Need Help to calculate the age of transaction

 

Hi All,

Can anyone please suggest an efficient method for calculating the age of a transaction...

I have a Transaction activity table and I need to calculate the age of each and every transaction.

 

TransactionID

TactivityID

Status

CRT_TS

T1

T100

Created

03/01/16

T1

T110

Open

03/25/16

T1

T120

Pended

03/05/16

T1

T120

Reopened

03/08/16

T1

T130

Closed

04/10/16

T2

T200

Created

02/01/16

T2

T210

Open

03/25/16

T2

T220

Pended

03/05/16

T2

T230

Closed

04/15/16

T3

T300

Created

04/01/16

T3

T310

Open

04/02/16

T3

T330

Closed

04/10/16

 

For a simple transition T3,

the age calculated by the networking days between CRT_TS of when it was opened and when it was closed.


e.g. - the age of Transaction T3 = NetWorkDays('04/01/2016',04/10/2016) = 6 Days

But the issue here is , if any one of the previous activity history is pended, then I need to deduct that many days from the total age.

 

e.g. For the transaction T1

NetWorkDays('03/01/2016','04/10/2016') = 32 Days


But the transaction was in pended state for 4 days (from 03/05/2016 to 03/08/2016). So the age of T1 would be 32- 4 = 28.

Any thoughts on how we can implement this logic efficiently.

 

The issue here is, I will have millions of records in the transaction activity table and I need to implement this in an efficient manner..

Appreciate any suggestions/thoughts on this.

 

Thanks

Nirmal.

 

3 Replies
swuehl
MVP
MVP

Maybe something like

SET DateFormat = 'MM/DD/YYYY';

INPUT:

LOAD TransactionID,

    TactivityID,

    Status,

    CRT_TS

FROM

[https://community.qlik.com/thread/213455]

(html, codepage is 1252, embedded labels, table is @1);

RESULT:

LOAD *,

  If(previous(TransactionID)=TransactionID ,If(Status <> 'Pended', Networkdays(CRT_TS,Previous(CRT_TS)),-1),0) as Duration

Resident INPUT

ORDER BY TransactionID, CRT_TS desc;

DROP TABLE INPUT;

TransactionID Sum(Duration)
74
T128
T240
T36
cjohnson
Partner - Creator II
Partner - Creator II

Hi Nirmal,

You can consider using the generic load‌ to get all stages/phases transposed. You can then do any calculation needed.

generic load result.png

Please see attached for more details. I do see a slight difference in your T3 answer -- but that's because the networkdays function returns 29 (not 32 as mentioned in your post).

Thanks,

Camile

cjohnson
Partner - Creator II
Partner - Creator II

Sorry -- didn't see this response until after I posted. This works too. The numbers are slightly different than the example I crafted below. Namely, I got 55 for T2. I think the reason is because I used Created to calculate the difference instead of Open. Additionally in his data for T2 we have it listed as Pended, but hasn't been listed as "reopened". As a result T2 isn't calculating appropriately. Niram will need to evaluate his data to see what he wants to do in that circumstance.

The only limitations I see are:

1. If for some reason he has other CRT_TS values in his data -- that would need to be accounted for.

2. What happens in situations where he has a "pended" crt_ts value but no re-opened value? How does he want that calculated? (limitations with both methods).


In any case -- attached is with the change to Open (instead of Created as I had down below).