7 Replies Latest reply on Jan 16, 2018 4:35 AM by John Hobby

    Stumped on simple LOD calc

    John Hobby
    John Hobby Jan 12, 2018 1:37 PM

      Howdy,

       

      So, I've been searching trying to find a solution to my problem and found sorta similar posts, but none that helps to address what I'm needing.  It could be that my search params were not correct, but tired of searching and know the great community here can help.

       

      I have data like this:

       

      patID     encID     svcDT     Code     pftID

      123          8923     1/1/18     24342     1761

      123          8923     1/1/18     L8699     1805

      123          8923     1/1/18     A4565     1972

       

      goal - create a single row for patID / encID / svcDt

       

      patID     encID     svcDT     Primary_Code     Secondary_Code     Third_Code

      123          8923     1/1/18          24342                    L8699                    A4565

       

      Current attempt

      Primary Code Formula

           if index() = 1 then WINDOW_MIN(min([CPT Code]), FIRST(), LAST()) END

       

      Primary Code Formula version 2

           {FIXED [patID], [encID], [svcDT] : min([Code])}

       

      The first formula isn't working right because the index just goes from 1...N for the entire data set.  I need it to reset for each patID/encID/svcDT combo.

       

      The second formula seems to be 'almost' there, but I need it to take into consideration the pftID which determines the ORDER in which the code should be (primary/second/third)

       

      I have a feeling I'm almost there, but starting to lose steam in finding help.

       

      I know someone out there has had to do this before

       

      Thanks!

      John

        • 1. Re: Stumped on simple LOD calc
          Joe Oppelt
          Joe Oppelt Ambassador Jan 12, 2018 2:37 PM (in response to John Hobby)

          This would be a whole lot easier to answer with an actual workbook and sample data set.

           

          Do you always have 3 [Code]s for every combo of patID / encID / svcDt ?

           

          you can make your index() reset every ( patID / encID / svcDt ).  Having the sample workbook would make it easier to show you.

          • 2. Re: Stumped on simple LOD calc
            John Hobby
            John Hobby Jan 12, 2018 5:02 PM (in response to John Hobby)

            Sorry about that ... here's a sample.

             

            the three primary columns should all be the same, while the codes can vary in number ... the attached has three patIDs ... i need a primary column with their first code based on the pftID order, a second column with the second code based on the pftID order... if it doesn't have one, then return a NULL or just empty string.

             

            John

            • 3. Re: Stumped on simple LOD calc
              Joe Oppelt
              Joe Oppelt Ambassador Jan 13, 2018 5:52 PM (in response to John Hobby)

              What constitutes “order” for s ?

               

              Tableau doesn’t have a concept of position within the data source.

              • 4. Re: Stumped on simple LOD calc
                Joe Oppelt
                Joe Oppelt Ambassador Jan 13, 2018 5:55 PM (in response to John Hobby)

                What constitutes “order” for patIDs ?

                • 5. Re: Stumped on simple LOD calc
                  John Hobby
                  John Hobby Jan 14, 2018 5:08 AM (in response to Joe Oppelt)

                  The pftID is considered the actual order of the records.  That field should drive the "order" for the patID / EncID / svDT combo.

                  • 6. Re: Stumped on simple LOD calc
                    Joe Oppelt
                    Joe Oppelt Ambassador Jan 15, 2018 6:42 PM (in response to John Hobby)

                    I think I understand what you are looking for.

                     

                    In the attached Sheet 1 is your original sheet.  (I threw PftID in there.)  I added a calc called INDEX.  And I set the table calc addressing so that it walks through the PftIDs for each PatID.  You can see 1,2,3 (or less if there are less than 3 for a PatID.)  This setting will be used for all the other calcs moving forward.  The key here is to restart every PatID.

                     

                    Sheet 2.  I made a First Code, Second Code, Third Code.  Again, I used those calc settings.  You'll notice that each respective value comes out only on the row it's found on.

                     

                    On Sheet 3 I made a copy of the three code calcs, to wrap the calc code in a WINDOW_MAX.  This shoves the value for the code on all rows within a PatID.

                     

                    Finally on Sheet 4 I dragged a copy of INDEX onto the filter shelf.  I have it display only index=1.  You may or may not need this step.  What really matters is that you have the value for each Code calc for each PatID.

                    • 7. Re: Stumped on simple LOD calc
                      John Hobby
                      John Hobby Jan 16, 2018 4:35 AM (in response to Joe Oppelt)

                      Many thanks Joe.  Thanks what I'm looking - the Sheet 4 - that is   I knew the index had to be involved somewhere.  I'm guessing the 'First, Second, Third' code formulas were just for demo purposes to show those calcs, as I'm not seeing them reference anywhere else.  If that's the case, that's cool how you broke it down for demo purposes.

                       

                      I'll have to play around with the index piece on the filter to see if it is needed and how the display is working.  But, technically - that is what I was looking for!

                       

                      Thanks,

                      John