1 Reply Latest reply on Jul 8, 2012 6:38 AM by Jonathan Drummey

    data blending & overlapping text

      I am testing a few basic principles on relationships and want to be sure I am aware of how to deal with this issue appropriately.

       

      I have two tables, one table is sales data and the other one is a calendar table.  When I create a relationship between the two tables based on the common key (date), it is a requirement that I use the date column on the calendar table somewhere on the Tableau view or the blending won't take place.

       

      As for this report I am not interested on displaying the date, I am required to use it on the 'level of detail' card for the data blending to work.  Unfortunately, this has the effect that when I report on a monthly basis, Tableau gets all date values and stack them on top of each other (generating overlapping text).  I am using a text table, not a chart for this report.

       

      The only way in which I can make this work is to change the relationship to blend at the monthly level.  This is a bit unfortunate as I have to go through the extra step of generating a calculated field in order to extract the year/month on each table, so I can use those columns for the new blending relationship.

       

      Is this the only way to deal with different levels of granularity when blending data?  Or is there a way to blend at the most granular level and not have to change the relationship when reporting at the higher levels, and still avoid overlapping text? (instead of overlap, I want to aggregate)

        • 1. Re: data blending & overlapping text
          Jonathan Drummey

          The way you described is one way, and likely the simplest to get the results you want. The good news there is that you only need to set up that calculated field once in each data source and then Tableau will take care of updating it when you refresh the data.

           

          An alternative to get rid of the overlapping text is to aggregate at a higher level by using table calculations, where there are two components done in the same calculation:

           

          1.  Create a table calculation that will return the same result for every row returned, so even though the text would still overlap, it would all be the same number and end up looking bold and slightly fuzzy in the table.

           

          2. Adjust the calculation so it only returns one value for one row in the partition, and NULL for everything else. This gets rid of the overlapping text.

           

          3 (optional). Adjust the calculation once more so that it is only computed once for the whole partition. This improves performance.

           

          So, for a basic SUM([Sales]) calc, the revised calculation that implements all three steps would be something like:

           

          IF FIRST()==0 THEN

               WINDOW_SUM(SUM([Sales]),IIF(FIRST()==0,LAST(),0))

          END

          Richard Leeke wrote about this method in a great post at http://www.clearlyandsimply.com/clearly_and_simply/2011/01/another-look-at-site-catchment-analysis-with-tableau-6-part-3.html

           

          Table calculations are completely dependent on the Compute Using settings and unless you have a good handle on that they can return unexpected and incorrect results, and two factors that can make them more complicated are blends and working with date hierarchies.

           

          You didn't give specifics on your data source(s), however one additional method is to do the left-join in your data source, either via Custom SQL in Tableau or as a query or stored proc in the database. This can let you get the blend at the right level of detail, not require too much granularity on the Level of Detail Shelf, and still be able to use the regular aggregates.

           

          I've found this latter method to be easier maintain than the table calculation approach. In my work, I have to do a lot of reporting on irregular event data such as the # of infections where a given unit in the hospital can go months or years without an infection. In order to make sure Tableau still shows the details for that unit, in the underlying query (we use MS Access for this data) generates a list of all month/unit combinations and then left-joins the event data (one row per infection event) to that. Then I have a # of Events calculated field that is something like SUM(IF NOT ISNULL([Event ID]) THEN 1 END) that is used in showing infections over time, calculating rates, etc.

           

          I hope this helps, and I'm interested in what other people have to say as well!

           

          Jonathan