6 Replies Latest reply on Apr 12, 2018 12:10 PM by Joe Oppelt

    Multiple IF Statements - I think....

    John Phillips

      So...I am at a loss as to how to phrase this...and searching has gotten me no where.  First of all, this is being done in Tableau Desktop 10.5.2.


      The data I am working with originally comes in the form of a text file.  There are case numbers that make them unique, but there can be multiple lines with the same case number because that case number had multiple service codes performed for them.  For instance, case 940 represents 1 line of data because it only has the one service code (based on the filter set in the workbook).  Even through case 4630 shows 3 service codes, that is not one line of data with 3 service codes on it...that represents 3 lines of data.


      I am wanting to create a report based around a particular group of service codes (initial encounters) and then referenced to a secondary group of service codes (measure values).  The goal of this report is so that we can look at our data dump and determine which case numbers have had an "initial encounter" but are still lacking a "measure value" so we can follow up with those cases and get a "measure value" done.  The hangup I am having is that both of these codes (initial and measure) are listed in the same column (service code) in the data set.


      Example of "initial encounter" codes

      - 121

      - 221

      - 1301

      - 2301

      Example of "measure value" codes

      - ABMI

      - CBMI

      - TOBACCO


      Hopefully that makes sense...and if not, I will try and expand on it more if need be.


      Thank so much in advance for any and all help!

      - John

        • 1. Re: Multiple IF Statements - I think....
          Joe Oppelt

          What differentiates an "initial value" from a "measure value" ?  If it's not something easily distinguishable with a calc of some sort, you would be better off making two dimensions:  [Initial Value] and [Measure value] in your data source.


          I see things like 99214A.  Would that be an initial value?  Would the first character being an integer identify an initial value?  Or are all initial values solely numeric, for example?

          • 2. Re: Multiple IF Statements - I think....
            John Phillips

            That is correct.  The "initial" values are actually federally mandated CPT codes, so they are all start numerically.  The codes that are all alpha are ones that we have internally for measure reporting.  Since the original data source is a text file, I am not sure there is an easy way for me to split the field...unless that is easily done with Tableau.

            • 3. Re: Multiple IF Statements - I think....
              John Phillips

              Let me explain in more detail on exactly what I am looking for.  The information below is the exact codes we are looking at for this report.


              If a case number has one of the following:


















              then I need to know if they also have one of the following:







              ACTUALLY, I need to know for the cases that had one of the first code group and they DID NOT have one of the second code group...that way our staff can follow up to get one of the second codes performed.  Does that make sense?

              • 4. Re: Multiple IF Statements - I think....
                Joe Oppelt

                I see what you are trying to do in [Initial Encounter].


                IF [Service Code] = '121'

                    OR '221'

                    OR '1301'

                    OR '2301'

                    OR '90791A'

                    OR '90791C'

                    OR '90792A'

                    OR '90792C'



                You need to do


                IF [Service Code] = '121'

                    OR  [Service Code] = '221'

                    OR  [Service Code] = '1301'

                    OR  [Service Code] = '2301'



                But if that list ever changes, you will have to keep editing that calc.


                You could instead do


                INT(MID( [Service Code],1,1))


                This is returning an integer value of the first character of that string.  If it returns null (assuming  [Service Code] is not null) then the first character is not numeric.  If not null then you have an integer.


                So look at [Initial Encounter row?] in the attached.  I displayed it on the sheet.  I identify any row that starts with an integer.  (If you must use only the specific list, then you can do the same logic, but change the OR syntax as I showed you.  The same result in the end.  You'll get a 1 on the rows you need to identify.  Note:  I made this calc a dimension so that we don't always have to aggregate it with SUM, etc., when we use it.)

                Next I did the first step toward the actual evaluation you need to do.  Take a look at [Died I have an initial encounter?] calc.  This is a FIXED LOD that say to look at all the rows under each case number and do the evaluation after the colon.  Essentially I am saying in this calc, "Are there any rows with a 1 on it?  If so set this calc to 1."  I put that on the sheet, also as a dimension.  Look at Case number 13239.  The value is 0.  There are no Service codes with integers.


                But look at 13408.  There are no integer Codes, yet we have a 1.  This is because the FIXED calc is doing the evaluation of the rows without concern for the filter settings.

                Go to Sheet 6(2).  It's a duplicate of the first sheet except for one thing:  I moved some of the filters "in context".  See screen shot:



                Tableau's order of operation has FIXED calcs evaluate after in-context filters, but before out of context filters.  In this case it turned out that the [Appointment type] filter has removed the rows with [service code] that begins with a numeric character.  You will see that 13408 now has a value of 0 as we need.


                Now you have a calc that tells you about all the rows for a given case number.  If any row has an initial encounter, the calc is set to 1 for every row of that case number (whether or not that specific row is an initial encounter.)


                Next you can make a second FIXED LOD that looks for specific measure values.  Go to Sheet 6(3) and look at [Do I have any measures I care about?]  This calc is modeled like the one for "initial", but it has a more complex syntax after the colon.  I did it for just two of your measure values.  You can expand it.  This will tell you if there are any measures you care about under that Case.  If it is greater than 0, then that many are in there.  And if the "initial" calc is > 0 and the "measure" calc is = 0, then you have  the guys you are looking for.


                (Note:  You could have also created a [Measure Values Row?] calc in the same way I created [Initial Encounter row?], and done [Do I have any measure...] calc using the same sort of MAX logic I used for [Do I have any Initial...].  If you do it that way, your output will be a 1 or a 0 instead of a SUM of the number of hits that you're currently getting.)  The point is that you want to have two evaluations at the [Case Number] level.  Your [Initial Encounter] calc is looking for


                Go to Sheet 6(4).  I made a calc to look for that, and put it on filters.

                • 5. Re: Multiple IF Statements - I think....
                  John Phillips

                  First of all...WOW!  Thank you so much, Joe.  This is exactly what I needed, and another example of two things.  #1 - why this community is so great to be a part of and #2 - how much I have to really learn to become a true data analyst and how much I HOPE to learn at TC18 this year!


                  Thanks again for going above and beyond.  If you happen to be at TC18...I owe you a drink buddy!


                  - John

                  • 6. Re: Multiple IF Statements - I think....
                    Joe Oppelt

                    Just buy me breakfast.