I'm having a tough time with a couple of LOD calculations. We have a scientific document management database that our lab scientists use. As they are running experiments they are creating a document for each. Multiple scientists contribute to each document, and the system automatically increments version number and revision number. Each edit increments the revision number, and once the document is approved, then the version number is incremented and the revision number is set to 0. This is how my data comes to me. I can then concatenate the version and revision numbers to display the version like this: 10.0, 6.3, 7.11, etc.

Here are my issues: I need to display the last version for the document regardless of which version I'm showing in a row. For example, I need to see all the documents that a scientist edited on a particular date, and I need to show the version he or she edited, and whatever the latest version is. So, on 4/13, John edited document 1026 version 6.4 and the latest version is 7.0. I'm required to display the latest version in that format: 7.0, 6.11, 10.24. I've tried various methods, but can't seem to make it work. Either I display the wrong last version when using MAX with a string value (9.3 instead of 10.0), or I display the correct last version using a numeric value by converting the version 6.3 string using FLOAT, but can't get it to format properly (7, 6.11, 8.1). In this last instance, I could fix the decimal place using the number formatting, but then it would display some of the versions incorrectly. I've also tried converting the decimal back to a string, but I'm getting 6.2999999999998 instead of 6.3.

On a related matter, I am also required to display the latest status of the document which is a string value ("In Process", "In Triple Check", "Approved", etc.). However, LODs only work with aggregates and you can't use the ATTR aggregate function, and using MAX returns the MAX alphabetically, which may or may not be the latest status.

Here is how I've approached it so far:

Last Revision Date = {FIXED [Unique Doc Number]:MAX([Revision Date])}

I then take the last Revision date and get the latest version or status: { FIXED [Unique Doc Number]:MAX([Verison Float])} or {FIXED [Unique Doc Number]:MAX([Doc Status])}

Attached is the workbook with data and my calcs so far. I would appreciate any help anyone can offer.

By the way, we are working Tableau 10.1 as that's what version our server is. We plan to upgrade to 10.3 once it's out of beta, but in the meantime I'm limited to 10.1.

Thanks, Bryan