11 Replies Latest reply on Feb 19, 2015 7:37 AM by KK Molugu

# How to return the 2nd smallest value

I have a set of data with phone numbers and time stamp of when there were called. How do I return the value of the 2nd time the phone number was called. I been trying to use minimum logic in my calculated field.

• ###### 1. Re: How to return the 2nd smallest value

Can you post a sample TWBX, along with a mockup of the results you're hoping to achieve in Tableau?  If your actual data is sensitive, you can copy a small subset of records into Excel, randomize, and use that as the basis for the TWBX.

A lot depends on what you hope to do with the value, and how you want to present the data to the users -- having a sample set of data and a mockup of what you'd like to see as an end-result will make it much simpler to show you a possible solution.

Cheers!

• ###### 2. Re: How to return the 2nd smallest value

Thanks, attached is an extract

• ###### 3. Re: How to return the 2nd smallest value

Hi,

Let me know if this helps

1 of 1 people found this helpful
• ###### 4. Re: How to return the 2nd smallest value

This helps, my end goal was to calculate the variance between the first call and the second call. Can you assist me with that?

• ###### 5. Re: How to return the 2nd smallest value

I've tried, but couldnt do it. Let me spend some more time and if i come up with something, i'll tell you.

P.S: Variance means the time difference between the 2 calls, right?

• ###### 6. Re: How to return the 2nd smallest value

Yes that is correct. I need to find the difference between the 1st and 2nd call. Thankyou for your assistance.

• ###### 7. Re: How to return the 2nd smallest value

Mike C;

Interesting that I just answered similar question yesterday for Johnny Hilgers and with exactly the same workbook. Check this link Returning second date for a dimension and there is more than just the second number he was looking for.

Hope this helps.

..kk

• ###### 8. Re: How to return the 2nd smallest value

Attached has the difference between the first and second calls (with data that I made up, I cannot open your workbook).

It has a listing of each call and the time between the first and second calls, using the Index() = 1 and a Lookup function.

The best I could get was a listing of each caller (phone number) and the difference for the 1st and 2nd (I did it in seconds)

Since this used a table calculation, you cannot use the two pass grand total to average the values.  I have tried a few different ways suggested by members of the brain trust in the past for similar problems, but could not get a bottom line average for you.

Not sure if it helps get you closer to where you want to be, but, I thought I would pass it along.

Link to Grand Total Customization Posts: http://drawingwithnumbers.artisart.org/customizing-grand-totals-part-1/

Link to further discussion of Grand Totals: http://community.tableau.com/thread/119639

The thing I cannot overcome to get a total for you is that the call level difference needs a table calculation (as far as I can see) and techniques to get you a grand total that would be the average of these rows dont allow a table calculation as an input, i.e. TOTAL and using something like a Running_AVG cannot do a compute using table down as the underlying calcs need a different compute using level to identify the right calls.

Voting up this idea: http://community.tableau.com/ideas/1604

couldn't hurt either.

As Jonathan pointed out in that idea thread, Level of Detail calculations are coming in version 9 and they may help here as well.  Still working on getting access to the beta

• ###### 9. Re: How to return the 2nd smallest value

Mark

Contact Jackie Clough she is managing the Tableau 9 beta program.

Tableau 9 Beta is Out! | Tableau Software

Cheers

Mark

• ###### 10. Re: How to return the 2nd smallest value

I already have the 9.0 and am currently using it.

• ###### 11. Re: How to return the 2nd smallest value

Mike:

Does the solution I mentioned above works for you?

..kk