3 Replies Latest reply on Oct 31, 2016 10:24 AM by Carl Slifer

    Display Zero for non-existent rows in a secondary data source?

    Laura Vecchio

      Good Afternoon!

       

      I'm absolutely positive this must have been covered in a forum discussion previously but I cannot for the life of me find an answer. And I'm desperate. Really desperate!

       

      I am connecting two data sources using a LEFT join (primary data source is Excel, secondary is MSSQL). I have been advised by Tableau online support that one solution may be to use an OUTER join however this option is grayed out.

       

      My primary source contains the budget sales figures for all our brands, the secondary is returning actual sales from our CRM database.

      I want to run a variance on budget vs actual however if there are no sales, there are no records (even a zero valued ones) which in turn means no budget figures will pull through.

      I believe this is purely because there is just no data and the join therefore can't be made. Tableau wouldn't 'assume' sales were zero if there were no records for Brand A in Oct-16 but how can I force it return a zero which will therefore allow me to use a calculated field to return the variance?

       

      I've tried using Table Layout > Show Empty Rows (columns etc) which was great for returning the Brand names that have zero sales, but still no zero values.

      As far as I understand I can't use ELSE or ZN, IFNULL etc. as there is no record there to process. Or at least, none of these seem to work for me.

       

      I'm going to struggle to load a sample workbook as I wouldn't know how to do this for customSQL or without jeopardizing company information so any help on the basis of the above would be hugely & gratefully received!!

       

      Thank you in advance
      Laura

       

      NB: I'm new to Tableau & SQL so please excuse my terminology if it isn't correct - & feel free to correct away!

        • 1. Re: Display Zero for non-existent rows in a secondary data source?
          Carl Slifer

          Hi Laura,

           

          This actually sounds like it might be a better use case for blending. You would use the excel as the primary data source and the mssql bit as the secondary datasource. 

          A few questions first.

          1) Is there every a time when the aggregations are different? One is a daily value and the other a monthly budget?

          2) Are there ever times when you have sales but do not have a budget for that month?

           

          You may wish to also look into domain padding in tableau or try this calculation.

          LOOKUP(ZN(SUM(Sales)),0) This forces the table to populate as when using a table calculation and an actual table it looks across all potential rows and columns. In our case we are looking up 0 places to the left or right so it looks at that exact cell and will return a 0 there are no values.

           

          Best Regards,

          Carl Slifer

          InterWorks

          1 of 1 people found this helpful
          • 2. Re: Display Zero for non-existent rows in a secondary data source?
            Laura Vecchio

            Thank you, Carl!

            I appreciate your help

             

            I tried the LOOKUP on my calc field however still no zeros.
            I have two calculated fields - 1 converting our 'deals done' to GBP using an IF ELSE (as we want it to ignore 'pending deals etc'), the next calc field would be the LOOKUP(ZN(SUM(Sales)),0) field - could the reason this isn't working be because rather than sales coming straight of out SQL, its sales from a calc field?

             

             

            This is then where I really show my lack of knowledge, how would you know if your data is 'blended' or 'joined'.
            Would Tableau assume blended data based on it being retrieved from two different data sources?

             

             

            I've attached a snipping from my DataSource sheet.

            Tableau311016.png

             

            In answer to your questions;

            1) No. Aggregation is always monthly. I have calculated fields which solve any daily, weekly billings issues.

            2) No. This is at brand level so we would have a budget value for each brand for each month, even if that was zero.

             

            Thanks again!
            Laura

            • 3. Re: Display Zero for non-existent rows in a secondary data source?
              Carl Slifer

              Hi Laura,

               

              You're joining right now: This is the link to how blending works:

              http://onlinehelp.tableau.com/current/pro/desktop/en-us/help.htm#multiple_connections.html

               

              Unable to really help more at this point without a look at your data. I'd

              be completely at the dark in telling you why things would be off as I'd

              just be guessing and pointing out common data issues. IF you read some of

              the posts concerning domain padding you might find your answer or if you

              can make a mock up within excel of your two data sources with your calcs

              myself or another fabulous person on these forums will happily assist you.

               

              Best Regards,

              Carl Slifer

              InterWorks

               

              On 31 October 2016 at 17:16, Laura Vecchio <tableaucommunity@tableau.com>