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?
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.
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?
I see what you are trying to do in [Initial Encounter].
IF [Service Code] = '121'
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.
BHC-QM Reporting A.twbx 5.7 MB
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!
Just buy me breakfast.