1 Reply Latest reply on May 3, 2017 9:20 AM by Bryan Sparks

    LOD Calculations with Strings or Dimensions as the object

    Bryan Sparks

      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

        • 1. Re: LOD Calculations with Strings or Dimensions as the object
          Bryan Sparks

          Well, this morning I was able to solve my own problem. I created a calculation that gets the highest version number for each unique doc number:

          {FIXED [Unique Doc Number]:MAX([Version Number])}


          Then I used the Last Revision Date and original Revision Date to get the last revision number and called it Revision Match:

          IF [Revision Date] = [Last Revision Date] THEN [Revision Number] END


          I then took an LOD of that and called it Last Revision Number:

          {FIXED [Unique Doc Number]:MAX([Revision Match])}


          Then I simply concatenate the two to display the version number in the proper format:

          STR([Last Verison Number])+ "."+STR([Last Revision Number])


          For the Last status, I did essentially the same thing. I created a field to get the status where the Last Revision Date matched the original Revision Date and called it Status Match:

          IF [Last Revision Date] = [Revision Date] THEN [Doc Status] END


          I then took an LOD of that for the Last Status:

          {FIXED [Unique Doc Number]:MAX([Status Match])}


          Since there is only one row that is not null for Status Match for each Unique Doc Number, this LOD now works properly. I hope this helps someone.

          2 of 2 people found this helpful