3 Replies Latest reply on Feb 11, 2013 6:56 AM by Robin Kennedy

    Combing multiple "Number of records"

    Oliver Beattie

      I have two charts at the moment; each shows a timeline with a "SUM(Number of records)" on each. Basically, I have one chart showing signups per day, and another showing cancellations per day. Each pulls its data from the same table but is showing the data as viewed from different date fields.

       

      I'd like to combine these into one chart (as well as adding another line to it for net gain), but am really struggling to do this since the dates are different fields.

       

      I feel as if I'm missing something really obvious. Could anyone help me out?

       

      Thanks

        • 1. Re: Combing multiple "Number of records"
          Robin Kennedy

          Hi Oliver,

           

          I believe what you need to do here is to duplicate your data source and blend it with your original source by using the relationship Signup Date = Cancellation Date

           

          Then you can draw your chart of signups per day by using the SUM(Number of Records) measure, and then you can also pull in SUM(Number of Records) again, this time from the secondary data source, to get the number of cancellations.

           

          Please see the attached for a similar example using the superstore data -- it shows the number of orders placed vs. the number of orders shipped.

          • 2. Re: Combing multiple "Number of records"
            Jonathan Drummey

            It's important to note that Robin's solution only works if, given the level of aggregation used in the blend, there is a row in the primary for every row in the secondary at the given value.

             

            So, using the example where Signup Date = Cancellation Date (which would effectively be at the day level assuming those are dates), then if there were no signups for 12 February 2013 then there would be no cancellations returned for that date, even if there were cancellations in the underlying data source. To work around this, you can:

             

            - create a scaffold data source with all dates, then either left-join or blend each data source to that scaffold.

            - use Show Missing Values on the date in the primary to force Tableau to pad out the days in the primary prior to the blend. This can be really powerful but can introduce issues

            - use a UNION instead of a blend

             

            Jonathan

            1 of 1 people found this helpful
            • 3. Re: Combing multiple "Number of records"
              Robin Kennedy

              Ah yes, a very valid point.

               

              A UNION was actually my initial thought here, but it seemed more complicated than a blend to implement.