6 Replies Latest reply on Dec 9, 2016 1:02 PM by Stephen Rizzo

    Blending based on calculated field

    Tara Villon

      Hello All,

       

      My problem is somewhat similar to Lookup using two data sources . However, my "score" is based on a calculated field for Top Box. Data is based on survey responses from 1-3.

      Sum(IF[Response]="3" THEN 1 Else 0 END)/SUM([Number of Records])

       

      Percentile rank is based on a large amount of data from thousands of organizations across the nation, so calculating percentile rank within Tableau is not an option. Our survey vendor provides us with an ancillary table saying when Top Box is X, percentile rank is Y (per question). This is updated and pushed to us quarterly.

       

      How do I blend these two data sources in Tableau? I am afraid I will have to completely restructure the percentile rank table... But it will require a lot of time as there are almost 40 measures, each with their own table.

       

      I have attached dummy data and workbook below.

      Please let me know if I have not shared enough, I am new to Tableau and the community!

       

      Thank you in advance for your time and guidance.

        • 1. Re: Blending based on calculated field
          Stephen Rizzo

          Hi Tara,

           

          To the best of my knowledge, blending on calculated fields is possible. See the post below for details.

           

          Blending on an aggregated measure?

           

          With that said, the issue you will most likely run into is that blending requires an equality constraint, whereas you really want to assign a top box score to a percentile rank if it falls within a specified range. As an alternative to this approach, I would recommend pre-aggregating your data in a custom SQL block, then joining it to the percentile rank table.

           

          EDIT: As someone who works for a survey vendor, if the issue is that your percentile rank tables are split into multiple columns, I would recommend asking your survey vendor whether they could provide the file in a more convenient format. You could probably figure out a way to restructure all of those tables yourself (either manually or using macros), but if this is a recurring upload you don't want to have to do the restructuring each time you get more data.

          • 2. Re: Blending based on calculated field
            Joe Oppelt

            Tara -- Just to understand this better -- Are you saying that you calc a value in your primary data source using a calc like the one you posted, and then you want to lookup [%ile Rank] from the secondary table based on the calc value from the primary source?

             

            Stephen -- To the best of my knowledge, you can blend on a calc that is a dimension, but not a measure.  And an aggregate calc will always be a measure.

             

            Tara -- Stephen makes an excellent point about the format of the file.  Have them add a column for question number, and put each percentile for each question on a separate row.

            • 3. Re: Blending based on calculated field
              Joe Oppelt

              Ah.   I looked at the link Stephen gave.


              The solution there was a FIXED LOD.  And a FIXED LOD can be a dimension, and is a dimension in the solution in the link.

               

              Tara -- You very well might be able to make that aggregate calc into a FIXED LOD.

              • 4. Re: Blending based on calculated field
                Joe Oppelt

                But you're still going to run into the issue that a successful blend requires data equivalence. 

                • 5. Re: Blending based on calculated field
                  Tara Villon

                  Thank you. It sounds like whether I achieve blending on that aggregate calc or not, a successful blend will still come down to the structure of the percentile rank look up table. However, they were very strict in terms of sending out "proprietary information" in an "non-editable format." (they only want to send these out as PDFs...!)

                   

                  But as you and Joe Oppelt kindly stated, a data blend requires data equivalence. I will have to keep prodding my vendor for a usable file format, as creating macros to reshape these files every quarter is an unwanted addition to my workload!!

                   

                  Thanks, guys!

                  • 6. Re: Blending based on calculated field
                    Stephen Rizzo

                    Glad to help! As for the percentiles, your best chance at getting the data in the format you want would probably be to submit a custom report request via your patient experience advisor. Hopefully that works out for you!