3 Replies Latest reply on Jan 15, 2016 2:18 PM by Carl Slifer

    LOD Level of Detail Expressions in a Many to One to Many relationship

    Mark Widawer

      Hello.

       

      I thought that I understood the FIXED Level of Detail expression when it was first released, and used it several times, but it does not appear to be acting as I'm expecting in this case. I think what makes this case special is that it's a Many-One-Many relationship...but even THAT I'm not sure of.

       

      I have prepared some sample data in an XLSX file and a TWB to demonstrate the problem.

       

      I have a list of three advertising methods (Flyers, Banners and Billboards) and how much money I spent each of 10 days for each method. So, it's a list of 30 records (3 methods X 10 days).

       

      I have a list of Eight leads that I acquired through those advertising methods: 2 from flyers, 2 from Billboards, and 4 from Banners.

       

      Four of those leads turned into buyers, and some have multiple invoices. And so I have a list of 7 invoices.

       

      What I want is a crosstab showing some pretty simple stuff:

       

      * How much did I spend on each Ad Method?

      * How many leads did I get from each ad method?

      * How much did each lead cost me?

      * What are my total sales for leads generated by each Advertising Method?

      * How much is each lead for each Advertising Method worth?

       

      The problem I am having is that when Tableau joins these three tables together, I end up with multiple rows for each day for each advertising method. I would have expected that setting a Level Of Detail to the Advertising Method (Ad Type field) would get me what I wanted. But it appears that is not the case.

       

      Here's a screen shot of what I expect to see in Tableau:

       

      And here's a screen shot of what I actually see in Tableau:

       

      Here's my LOD calculated field for "Total Ad Costs" as an example: {FIXED [Ad Type]:sum([Ad Cost])}

      I should get $110, but I'm getting $770 instead ($110 * 7 invoices total).

       

      And here's my LOD calculated field for "Total Sales": {FIXED [Ad Type]:sum([Invoice Amount])}

      I should get $15,500, but instead I'm getting $155,000 (the number I should be getting times the number of days of ads).

       

      This has me stumped!

       

      It's a very simple, small example, and I can't believe this is a very unique type of report...but it has had me stumped for the better part of a day.

       

      Can anyone explain why and/or how to fix it?

       

      Thank you in advance for your help!

       

      -Mark

       

      p.s. In the sample XLSX, there are three sheets (tables). Advertising, Leads, and Sales

      Advertising relates to Leads by the AdType column.

      Leads relates to Sales by the Email column.

      They're all joined together in the TWBX file.

        • 1. Re: LOD Level of Detail Expressions in a Many to One to Many relationship
          Carl Slifer

          HI Mark,

           

          It was an issue with your join, so not a Tableau issue this time.  The issue is that you ended up with duplicates. It was easy to find only because of your expected value for ad costs were multiples of themselves. And they were multiples to the degree of either the count of leads and count customers.

           

          In this case (at least in my quick look) you won't be able to join everything because the data is in the most perfect style. So we need to blend.

           

          You can look up Tableau Data Blending, it can be a mild tricky bit to understand.. But enjoy the workbook.

          PS. Your Number of Invoices should be 6 for banners, not 5. It took me a while to figure it out

          Bob (1), Jill (3), Mac (2), and Milton(0) were all Banner Leads

           

          This is a hodgepodge and works in this instance and you may want to supply the invoice number at the database for each level someday. This way you have a unique identifier (Star Schema).

          Cheers

          Carl Slifer

          InterWorks

          • 2. Re: LOD Level of Detail Expressions in a Many to One to Many relationship
            Mark Widawer

            Hello Carl.

             

            GREAT! Thank you for your thoughtful reply (and for catching that error)!

             

            That prevented several more hours of beating my head against the wall.

             

            I duplicated your BlendAndJoin worksheet easily enough. Thank You! Applying the same concepts to the live data is proving a bit more challenging, though. Tableau gives me errors saying that I should use the Data | Edit relationships function instead of letting Tableau create the relationships automatically. Even after I create the relationships manually, I still get the same error...and it continues to pop up every time I recalculate (or add a new measure, or change anything). Note: As I write this, I'm not getting that error, so I'll have to figure out what has changed and will report back later.

             

            But there's another problem: In my real data, each of the advertising methods is a specific instance of a more general type of advertising. For example, imagine if Banner were classified as "Online" advertising, and Billboard and Flyers were "Offline" classes.

             

            I very much want to roll up the data and report on the effectiveness of Online vs Offline data, but it looks like if I remove the joining field (in the example, that is AdType) from the Viz, then the Join is broken.

             

            I've even tried building a hierarchy between AdClass and AdType, but that didn't fix the problem. If AdType isn't visible in the Viz, it breaks.

             

            Is there a way around this problem, too?

             

            If that's not clear, here's what I would expect the output to look like (assuming my arithmetic is correct):

             

            In the new sample data, after I add AdClass and remove AdType, I get a message saying that there is no relationship between the two tables. Specifically, "Fields cannot be used from the Leads & Sales data source, because there is no relationship to the primary data source. In the Data windows, switch to the Leads & Sales data source, and click at least one link icon to blend these data sources."

             

            So the question is...Is there a way to blend using a column that is in both data sources, but not visible in the Viz?

             

            -Mark

            • 3. Re: LOD Level of Detail Expressions in a Many to One to Many relationship
              Carl Slifer

              Howdy Mark,

               

              You can blend on columns that are not in the viz. However it sounds like even though you've defined a relationship using the edit data source you have not 'confirmed it'.  There are many possible relationships and in fact there were probably relatonships when you added a custom one too it. But in the data pane you actually have to click the link, red paper clip, etc to establish that blend for that specific sheets. Possible data relationships are global within a workbook but to use any given blend is specific to the sheet. 

               

              The text popping is one of the few data cases in all of the world that tells you exactly what to do.