9 Replies Latest reply on Sep 4, 2018 7:32 AM by Simon Runc

# Calculation: Looking up the lowest turnaround time (including blanks) for each unique ID

Hello,

Very sorry if this is already posted, but I couldn't figure this one out myself, any help is much appreciated, thank you!

See attached Excel... here is some context:

- Each unique ID created should have an identifier that is added either immediately or later

- Column D gives the days it took for the identifier to be added to the unique ID HOWEVER there are duplicates in the data

- I want a calculation that tells me for each unique ID (22 in total in this example) what the turnaround time was (i.e. give me results such as 0 days, 15 day or not yet available)

I have tried these but it does not give me the correct result as it double counts

{FIXED [Unique ID] : MIN([Days for Identifier to be added to the Unique ID])}

Also tried this:

IF [Days for Identifier to be added to the Unique ID] = 0 THEN "IMMEDIATE"

ELSEIF [Days for Identifier to be added to the Unique ID] > 0 THEN "LATER"

ELSE "NOT YET AVAILABLE"

END

• ###### 1. Re: Calculation: Looking up the lowest turnaround time (including blanks) for each unique ID

hi Kamin,

Your formula seems to work for me. Let me know where it's not doing what you expect and I can take another look.

Also not sure what this means (couldn't find the field [Loan X TAT?] )?

IF [Loan X TAT?] = 0 THEN "IMMEDIATE"

ELSEIF [Loan X TAT?] > 0 THEN "LATER"

ELSE "BLANK"

END

1 of 1 people found this helpful
• ###### 2. Re: Calculation: Looking up the lowest turnaround time (including blanks) for each unique ID

Hi Simon,

Thanks for your prompt response. I agree that it does sort of work but there are duplicates which is the issue that I can't seem to resolve

As an example the below unique ID is included 4x in the report. The turnaround for the identifier to populate for this unique ID is 21 days. However, the calculation that I used would count this as two turnarounds, 21 days and another as no yet available. As a result, my unique ID counts are inflated.

 Date Updated Unique ID Identifier Days for Identifier to be added to the Unique ID 03/08/2018 96665906 03/08/2018 96665906 03/08/2018 96665906 24/08/2018 96665906 110 21

Hope this helps clarify the issue.

• ###### 3. Re: Calculation: Looking up the lowest turnaround time (including blanks) for each unique ID

Not sure I fully understand.

What level of detail are you trying to display (or what are you trying to calculate, you mentioned a COUNT)? In the above example, what would you like to see? Just the last row?

• ###### 4. Re: Calculation: Looking up the lowest turnaround time (including blanks) for each unique ID

Sorry that I'm being unclear. So in the Excel attached, there are 22 unique IDs. For each of these unique IDs, I would like to know the current status in terms of turnaround times (displayed as 0 days, 1 day, 2 days etc. or just as not yet available)

When I use that formula, the counts do not add up to 22 as some unique IDs are being counted twice. The below shows 15+7+2+1+1+1 = 27. There are 5 additional counts for instances where the identifier was not immediately available, but later become available

• ###### 5. Re: Calculation: Looking up the lowest turnaround time (including blanks) for each unique ID

Ah I see.

So I think you just need to use COUNTD rather than COUNT

• ###### 6. Re: Calculation: Looking up the lowest turnaround time (including blanks) for each unique ID

Sorry Simon, could you try uploading the workbook again? i am getting this error message... Thanks!

• ###### 7. Re: Calculation: Looking up the lowest turnaround time (including blanks) for each unique ID

what version are you using?

• ###### 8. Re: Calculation: Looking up the lowest turnaround time (including blanks) for each unique ID

Simon you're absolutely right, my original formula does actually work fine. I had input the wrong identifier (I have over 10 types in my workbook) within the calculation.

Thanks very much for your patience!

• ###### 9. Re: Calculation: Looking up the lowest turnaround time (including blanks) for each unique ID

No problem, easily done. Glad you got to the result you wanted