5 Replies Latest reply on Jan 3, 2018 11:11 PM by Amit Kumar

    Lookup from different datasources with LOD calculations?

    Amit Kumar

      Hi,

       

      I have a calculation in sheet 1(col: Buffer) from one data source, and also have another sheet (named: sheet2) with another data source where i want to get the value of buffer which was calculated in sheet1.

      I have performed a join based on the dimension i need the data, in this case Prod Line Group. But when i am trying to match the result, i get a different value of Buffer in sheet2 as compared to sheet1.

      For ex. in sheet1 say for PC i have value of Buffer as 11 but in second sheet instead of getting 11 i am getting value for PC as 7.9.  Can someone explain how to get my expected value.Attached is the .twbx file.

       

      Note: the calculation have LOD expressions.

       

      Thanks in Advance

      Amit

        • 1. Re: Lookup from different datasources with LOD calculations?
          David Bull

          Hi Amit,

           

          It looks like it's because of different filters being used between the two sheets. I took all the filters off both sheets and got the same value (7.93) for PowerCenter.

           

          You're using an 'INCLUDE' LOD calculation - which means it is being calculated after the dimensions on your filters shelf. See Filters and Level of Detail Expressions

           

          Hope that helps.

           

          Thanks,

          David

           

          P.S. It's also worth bearing in mind that the datasources are technically being blended rather than joined - which will lead to slightly different behaviour when it comes to using LODs. This thread has a bit more info on that: how to aggregate blended measures?

          2 of 2 people found this helpful
          • 2. Re: Lookup from different datasources with LOD calculations?
            Amit Kumar

            Thanks David. Yes you are right, both the sheets have different set of filters(because of multiple data source) but unfortunately this is the requirement i have.

            I did scrum through various threads but could not find any similar issue. Thanks Anyways.

             

            Amit

            • 3. Re: Lookup from different datasources with LOD calculations?
              Amit Kumar

              Jonathan Drummey- Jon, any idea how can i get this resolved.

               

              Thanks in advance

              Amit

              • 4. Re: Lookup from different datasources with LOD calculations?
                Jonathan Drummey

                Hi Amit,

                 

                FYI I prefer Jonathan to shortening my name to Jon, thx!

                 

                First of all going back to what David wrote the two sheets are apples and oranges, besides being from different data sources (with different Prod Line Groups) there are filters on one that are not on the other. So to get the same results in both sheets the underlying data feeding into the calculations MUST have the same filtering in place.

                 

                Now the Sheet 2 is built using a data blend. Tableau data blends are not a join exactly, that's an oversimplification. A more useful simplification is that data blending is a kind of post-aggregate join and that imposes limits on what kinds of calculations and filtering we can do. So, for example, If I bring in a dimension filter from APAC... source into Sheet 2 to filter for the same Ps Titles used in Sheet 1 then the Buffer calculation breaks because some of the sub-calcs are using COUNTD() and COUNTD() is one of the functions that has more limited use when using data blending. I've done some presentations on this in the past, if you want I can look them up.

                 

                So to get the desired results you're going to need to do some data prep in order to have Sheet 2 have the same results. Given the complexity of the calculations I'm strongly leaning against using a data blend, my suggestion would be to figure out a way you can do some sort of join (potentially an outer join) or union on the tables and then build measures from there, potentially using LOD expressions to deal with replicated rows that are likely to result.

                 

                Jonathan

                1 of 1 people found this helpful
                • 5. Re: Lookup from different datasources with LOD calculations?
                  Amit Kumar

                  Makes sense, will try to figure this out. Thanks Jonathan for your quick response.

                   

                  - Amit