2 Replies Latest reply on Mar 28, 2016 8:55 AM by Paraskev Bentchev

    Blend to Show Columns from Different Aggregation Levels

    Michael Lance

      Hello,

       

      Given the data I have:

        

      HospitalPatientsRegionDivision
      A3581X
      A3351X
      A9381X
      A171

      X

      ...where hospital, region and division values vary (for me, this table is actually coming from an Oracle connection)

      ...and another table that I use for blending (connected via "Hospital" to get the "Status" field - for me this is an Excel file):

       

      StatusHospital
      InternalA
      InternalB
      InternalC
      ExternalD
      ExternalE
      ExternalF
      ExternalG
      InternalH
      InternalI
      Internal

      J

       

      ...I notice that in order to use "Status" (after blending), I have to make it an ATTR() function. This is one potential issue.

      Moving on...

       

      I want to create a view that displays, for example, columns of Regions for hospitals where "Status" is "internal" but if the "Status" is "external," I want to display the hospital name instead.

      Sort of like this (imagine these as columns) with "Patients" as the measure:

      (Region) 1.....(Region) 2...Hospital E....Hospital F

       

      -Also imagine that hospitals E and F are the only "external" hospitals

      -I will eventually want to create a case statement with a parameter so the user can .select division, region, or hospital to alter the view. The hospital view just shows all hospitals regardless of "Status."

       

      Is this possible?

       

       

      Thanks,

      Michael

        • 1. Re: Blend to Show Columns from Different Aggregation Levels
          Bill Lyons

          This definitely creates a challenge. As you experienced, any field from a secondary data source in a blend must be aggregated. (ATTR() is a special form of aggregation used for non-numeric values. It compares the MIN() and MAX() values of the specified field, and returns the value if they are the same, and an asterisk (*) if they are different.) Because of the aggregation, any fields which are formed by a calculated field incorporating the secondary data source must be a measure. In your case, you want it to be a dimension. I'm guessing this the challenge you encountered before posting the question. (You can see my attempts in Sheets 1, 2, & 3.)

           

          The only solution I could come up with is to create two sheets, one filtered for External hospitals only, with Hospital on columns, and one filtered for Internal hospitals only, with Region on columns. Then, put the two sheets side by side on a dashboard (Dashboard 1 in the attached example).

           

          I don't know if that will be sufficient for you, but that is the best I can do with Tableau 9.2. If/when a future version supports a true join between disparate data sources, this problem can be resolved. v9.3 does have some ability to do this, but I believe it is limited to Excel and text file sources. If I am wrong, I will be overjoyed to be wrong!

           

          I am sure you could accomplish this with an external tool, such as Alteryx.

          • 2. Re: Blend to Show Columns from Different Aggregation Levels
            Paraskev Bentchev

            At least Hospital  should be in level of detail to not see *

             

            What I did is first I created new field Status in the first datasource

            ATTR([Status (Roll-up Question 2)].[Status])

             

            and then create new calculated field (only with aggregate values)

            IF [Status] = 'Internal' then STR(attr(Region))

            else attr([Hospital])

            end

             

            If you have to sum all hospitals in region 1 for example you have to generate somehow this list in third datasource and start vizualization from it

            I mean you have to start with datasource that is with all dimensions that you want to blend and then you can create whit parameters how to be shown

             

            Hope this helps