5 Replies Latest reply on Dec 19, 2017 7:22 AM by Stephen Bava

    Blended Data LOD Calculations

    Stephen Bava
    Stephen Bava Dec 16, 2017 5:23 AM

      After some research, I have learned that using a LOD on blended data isn't possible. It appears that Window calculations are the answer. It is imperative that this is done as a DATA BLEND and not a JOIN. The actual data set I use is huge and a join is too slow. Data blending works much faster.

       

      However, I am still having issues. In the attached TWBX, I have an example. I am trying to show where my primary data source is missing a match to my secondary data source. I have matches set to read "Review Complete", and non-matches to read "Review Needed". I would like to create a pie chart that shows the comparison between the two. However, the count must be on unique values only. E.G. if there are 40 rows of data for Aaron Bergman, this should count as 1. Take the unique count of "Review Needed" and the unique count of "Review Complete", and turn those into a pie chart base upon total unique count.

       

      Below is a screenshot of what I am trying to get to. Any help is appreciated!

       

      • 1331 Views
      • Categories: Calculations, Data Sources & Connections
      • Tags:
        • 1. Re: Blended Data LOD Calculations
          Simon Runc
          Simon Runc Ambassador Dec 19, 2017 5:07 AM (in response to Stephen Bava)

          hi Stephen,

           

          So this is one of the drawbacks with Blending....COUNTDs! I'm surprised that blending is so much more efficient than Joining (maybe look at the indexing in your DB)...but on to the problem at hand!

           

          So Blended fields always come in as an aggregate, and COUNTD need the COUNTD level in the Viz in order for it to work. However we can use a clever trick to have Customer in the VizLoD, but only return a single mark for each status.

           

          First I created this formula

          [Customer COUNTD]

          WINDOW_SUM(COUNTD([Customer Name]))

           

          and ran this at the customer level. However your dimension (Review Complete/Needs Review) is an aggregate calculation (it has to be as you are blending), so a Table Calculation (by default) won't use this (aggregated) dimension to partition the data. However this is a (little known) option to tell Tableau to include it

           

          Untick "Ignore in Table Calculations"

           

           

          So now we have the right counts for each status, but we have this value (mark) repeated for every customer...so one last little trick; We add the following to our calculation

           

          IF FIRST() = 0 THEN

          WINDOW_SUM(COUNTD([Customer Name]))

          END

           

           

          This has the effect of only returning a value for the 1st mark (as there is no else all other marks equate to NULL, and so don't get plotted)

           

          Finally is the %age, which I did like this

          IF FIRST() = 0 THEN

          WINDOW_SUM(COUNTD([Customer Name]))/SUM({COUNTD([Customer Name])})

          END

           

          and as I've used an LoD to get the number of all customer, I also had to add your segment filter to "context" so it would affect the FIXED LoD.

           

           

          hope that helps and makes (some) sense

          2 of 2 people found this helpful
          • 2. Re: Blended Data LOD Calculations
            Norbert Maijoor
            Norbert Maijoor Ambassador Dec 19, 2017 6:36 AM (in response to Simon Runc)

            Hi Simon,

             

            Always nice to get a "precious gift" during the holiday season.

            Be ensured it will be stored in my "Treasure House".

             

            Enjoy Christmas together with your loved ones

             

            Regards,

            Norbert

            • 3. Re: Blended Data LOD Calculations
              Simon Runc
              Simon Runc Ambassador Dec 19, 2017 6:48 AM (in response to Norbert Maijoor)

              Thanks Norbert

               

              I actually stole (and then re-gifted!) this one from Jonanthan!! (...who else!), but it is the season for sharing

               

              Have a great Xmas too.

              • 4. Re: Blended Data LOD Calculations
                Norbert Maijoor
                Norbert Maijoor Ambassador Dec 19, 2017 7:00 AM (in response to Simon Runc)

                Hi Simon,

                 

                hihihi..You have even "stolen" the "business model" of our beloved Father Christmas...but most important it all ends up in my Treasure House where it will be treated with great care:))))

                 

                Regards,

                Norbert

                • 5. Re: Blended Data LOD Calculations
                  Stephen Bava
                  Stephen Bava Dec 19, 2017 7:22 AM (in response to Simon Runc)

                  That is incredible! Works perfectly. Thanks for taking the time to answer this. You just solved a lot of time and future efforts.