13 Replies Latest reply on Oct 13, 2016 11:50 AM by Vu Nguyen

    Blended Data Missing Values

    yigaelchetrit0

      Hi,

       

      I am blending 2 data sources and when I look at the total spend by year, the total amount is correct by year. When I add in an additional dimension that the 2 sources are linked by (GL), the total for 2012decreases by $24,260. It looks as if 2 GL values are being excluded (5315 - Radio and 5345 - Public Relations) in 2012 when I add in the GL dimension.

       

      The packaged workbook is attached. Correct total is on the "Year Only" tab and the incorrect totals are on the "With GL" tab.

       

      Not sure why this is happening.

       

      Any help would be greatly appreciated.

       

      Thanks,

      Yigael

        • 1. Re: Blended Data Missing Values
          Matt Lutton

          Your workbook file is huge.  I would suggest trimming down workbooks and only including the necessary fields and sheets when posting on this forum.  Any additional content that isn't needed can be a distraction from the problem at hand--and this workbook takes a long time to load on my machine.

           

          Here's more helpful info on posting to the forums:

          http://community.tableau.com/message/213297#213297

          • 2. Re: Blended Data Missing Values
            yigaelchetrit0

            Sorry - still working on getting a better handle on how to reduce the workbook size correctly. As for the data not being blended in the sheets in question. That's confusing me further as I purposely selected fields from one data source only and am seeing the same incorrect result when GL is added.

            • 3. Re: Blended Data Missing Values
              Matt Lutton

              It appears this is because you do not have GL activated as blending field on the "Year Only" sheet, so the GL dimension is not being considered in your Year totals.  If you click to activate the blend on GL, you will see the same results in the "Year Only" sheet.

               

              UNLINKED (no blend on GL field):
              'Unlinked Field.png

               

              LINKED (blend on GL field enabled):

              Link Turned On.png

               

              This is expected behavior--the level of granularity for your data blend has to be set on each sheet in the workbook.

              • 4. Re: Blended Data Missing Values
                yigaelchetrit0

                The problem is though that the data on the Year Only tab is the correct amount. The total shown on the With GL tab is incorrect, as it is missing values. I want to be able to include the missing values on the With GL tab so that the total matches with the Year Only tab.

                • 5. Re: Blended Data Missing Values
                  Matt Lutton

                  Again, this is because you've blended on the GL field in the "With GL" tab and NOT on the Year Only tab.  This is expected behavior.  The two cannot possibly match when you are blending on GL in one, and not blending on GL in the other.

                  • 6. Re: Blended Data Missing Values
                    yigaelchetrit0

                    I get that part, but what I don't understand is why values are being excluded from the With GL tab if they exist in the data.

                    • 7. Re: Blended Data Missing Values
                      Matt Lutton

                      Because you are blending on the GL field in the "With GL" tab...

                       

                      In the By Year tab, the GL field is not being considered in the level of detail.

                       

                      Data blending creates a link on common dimensions--where those commonalities do not exist in the two data sources, values will be excluded.

                       

                      The two values you mentioned, 5315 - Radio and 5345 - Public Relations, do not appear in your primary data source for 2012, but do appear in the secondary data source, so they cannot be included in the data blend.  This is very easy to spot when we view the GL values in both data sources on separate sheets in Tableau--the screenshot below shows your Primary Data Source.

                       

                      If you want those values to be included, you'll need to add them to the Primary Data Source, with $0 amounts, so Tableau can use these dimension values in the data blend.

                       

                      Primary Source GL Fields2.png

                       

                      I hope this makes sense. 

                      1 of 1 people found this helpful
                      • 8. Re: Blended Data Missing Values
                        yigaelchetrit0

                        Ok - thanks for your help!

                        • 9. Re: Blended Data Missing Values
                          Matt Lutton

                          You might want to study some of the information available at the links provided here:

                          http://community.tableau.com/thread/137829

                           

                          Data blending can be difficult to understand, for certain.  If someone else wants to try and explain what is happening in the OP's workbook, please feel free--my explanations are probably not as good as a more experienced user's would be.

                           

                          If you could create a true JOIN between the two data sources, you could resolve this problem. However, both data sources need to be the same type in order to use a true JOIN--I tend to create Views or Stored Procedures to avoid the many implications of data blending.  Data blending is different from a true JOIN, and that's important to understand.

                          • 10. Re: Blended Data Missing Values
                            Phillip Burger

                            The secondary data source does not contain information for year 2014. The primary does.

                             

                            It looks like the blend is working as it should.

                             

                            As a starting point, I would suggest that both the primary and secondary data sources contain the same set of year and gl values.  

                             

                            The attached workbook includes sheets 30 and 31. These show that the year and gl in the primary and the year and gl in the secondary do not match.To assist in understanding the difference in dates between the two sources, I renamed the Date in the primary to DatePrimarySrc and the Date in the secondary to DateSecondarySrc.

                            • 11. Re: Blended Data Missing Values
                              Phillip Burger

                              I second this. Push the joins into the database if you can. Next choice is custom SQL if they are from the same database. Only if they are really two different data sources, like maybe one is from a database and the other a text file, for example, would I do a blend.

                              • 12. Re: Blended Data Missing Values
                                yigaelchetrit0

                                Thanks! I added a record with Null values for each of the missing items into the primary data source and am now seeing all the values that need to be there.

                                 

                                As a side note, more in-depth content on blending would be a great help. Great suggestion.

                                 

                                Thanks,

                                Yigael

                                • 13. Re: Blended Data Missing Values
                                  Vu Nguyen

                                  Hi, I have exact same problem. Is there a workaround beside adding 0 to missing values? I have quite a few missing values, adding them all is not possible.