2 Replies Latest reply on Jan 25, 2019 5:56 AM by Chad McLaughlin

    Calculated field with text result shows as measure

    Chad McLaughlin

      Hi all,


      I'm stumped with the behavior of my calculated field, and I hope you can help!  The Social Work department at my hospital maintains a manual spreadsheet tracking patients in a community care partners program.  The spreadsheet contains patient identifying data, the date the patient agreed to be in the program, and if the patient is no longer in the program, the closing date.  We would like to bump this manually tracked data by patient against our EMR to identify those encounters (visits) that occurred when the patients were actively in the program, as well as the encounters that occurred shortly before the starting dates and shortly after the closing dates.


      In my packaged workbook attached, the primary source is simulated EMR data with one record for each patient encounter.  The secondary source is a simulation of the manually updated spreadsheet with one record per patient.  In the primary source, my calculated field that references the service date from the primary source and the starting and closing dates from the secondary source is called "CCT Category 3".  This field should categorize the encounters into pre, active, and post, and though I set the results as text responses, it appears as a measure.  It works nicely when I look at the data by encounter, as shown on the first tab.  But if I try to remove the encounter ID's to get subtotals by category, I get no results, as shown on the 2nd tab.  I believe if I can get this calculated field to appear as a dimension, my problem will be solved.  And I wonder if there is a limitation with functionality concerning dates from a secondary source.  As you can see, I have also tried enclosing the entire calculated field in a STR function, which had no impact. 


      Thanks for any insight you can provide!