9 Replies Latest reply on Jul 29, 2016 1:05 PM by Stoyko Kostov

    Display 2 sets of dates as part of a single chart

    Neil Powling

      Dear Everyone

       

      Apologies if this is simple, but I can't work out precisely how to acheive the result I'm after.

       

      I am trying to plot a series of events (In this case, OFSTED Inspections) over the top of a set of numbers (in this case, numbers of children in care.

       

      • OFSTED Inspections have occurred since 2006 on specific dates (e.g. 1st January 2011). There are a number of 'domains' (areas of focus, which have varied over the years) and a number of judgements (always a scale from 1-4, but the gradings\sequence are inconsistent)

       

      • Numbers of children in care are released as at 31 March. I have the data going back to 2006.

       

      • I want to have:
        • a line chart of numbers of children in Care
        • with a bar chart at the appropriate date showing the domains and judgements,
        • for each of the 152 local authorities in England.

       

      In the attached (with data) I have created what I am after in seperate worksheets, but am not able to overlay them.

       

      I have tried various combinations of dual-axis, continuous/discrete measures, exact dates vs month\year, blended\non-blended data, items in the different shelves - but in all cases:

      • One of the dates reverts back to the 19th Century and\or
      • Only part of the data is shown

       

      Can anybody help with suggestions? For reference, I am using Tableau 9.3 (latest point update)

       

      Many thanks in advance,

       

      Neil

        • 1. Re: Display 2 sets of dates as part of a single chart
          Stoyko Kostov

          Hello Neil,

           

          Instead of using 2 separate data sources, I think it would be better to join them. They point to the same database.

           

          It seems like you should join on [DfE LA Name] = [Geog N] *and* [Publication Date] = [DateAt].

           

          In fact, this is the recommendation Tableau gives you when you try to drag [No CLA At 31 Mar] to the Rows section in Sheet 4.

           

          Once you have that, I believe you will be able to overlay different measures, as long as your axis is a Date (it would be either Publication Date or DateAt, which would be equal by virtue of the join).

           

          I wasn't able to do that join on the extract - but if you join them on the original data source and provide a workbook with the new extract, I should be able to help further.

           

          Hope this helps!

          • 2. Re: Display 2 sets of dates as part of a single chart
            Neil Powling

            Hi Stoyko

             

            Thanks for the help. I had considered that, but because I'm not quite au-fait with how Tableau deals with multiple rows I thought it would be clearer to have two data sources. Your join is pretty much right, although I have some reservations

             

            The reason I'm a little concerned is that the date feilds are not equal

            • the inspection date can be at any point during the year, and is quite inconsistent in terms of volume (some authorities may have two inspections in a year and then none for two years afterwards)
            • However the childrens data is very consistent and regular - it's for every authority at the 31st March every year
            • Therefore if I were to do an equal join in SQL I'd end up with a null query

             

            The solution (it seems to me) would be for join on the DfE Name alone - which would return an essentially cartesian product (i.e. one row of inspection data for each row of child data). This situation therefore strikes me as something that would add complexity to the work to get it visualised Tableau.

             

            I'll try and get a new version of the workbook up in a couple of hours to see the lay of the land.

             

            Neil

            • 3. Re: Display 2 sets of dates as part of a single chart
              Stoyko Kostov

              Hi Neil,

               

              Don't let null values bother you. Tableau should handle them just fine and visualize only the non-null values for the Inspections columns.

               

              To overlay the charts, you do need a single date axis. If you have particular concerns about the join, please ask - I'll try to help.

              • 4. Re: Display 2 sets of dates as part of a single chart
                Neil Powling

                Hi Stoyko

                 

                Thank you for this - I had both an urgent deadline, and the data in the underlying database needed a bit of manipulation to get it to the right point.

                 

                I did what you suggested and brought the child data into the same query as the inspections. When I used an exact join, it was p[retty much as I expected - I got no results. So at present, I'm using a full outer join (which seems like the most logical thing to use at present). However my SQL background is making me think there looks to be an awful lot of null values.

                 

                Once I'd done this, I still couldn't get the data to overlay.

                 

                I'll try to upload the revised work book but looks like I need to reply back to the original question.

                 

                Neil

                • 6. Re: Display 2 sets of dates as part of a single chart
                  Stoyko Kostov

                  Hi Neil,

                   

                  You are still unable to overlay the charts because the dimension you used for the date axis - Publication Date - is not related to the second measure you want to plot - No CLA at 31 Mar. This means that for non-null Publication Dates No CLA at 31 Mar is always null, and vice versa - for non-null No CLA at 31 Mar Publication Date is always null.

                   

                  You need to find a date dimension which will contain (at least some) non-null values for both measures you need to overlay. I suggest you add a calculated field DateUnion defined as IFNULL([Publication Date], [Snapshot Date]), and replace Publication Date in your Columns with this.

                   

                  Also, my suggestion to join on both [DfE LA Name] = [Geog N] *and* [Publication Date] = [DateAt] may have been incorrect.  Using just [DfE LA Name] = [Geog N] could be a better clause for the join. Give it a try.

                   

                  Because I can't change the join, I defined another calculated field that you may not need: GeogUnion, defined as IFNULL([DfE LA Name], [Geog N]). I replaced DfE LA Name in the Rows of the dashboard with it.

                   

                  This gave me the 2 measures overlayed, but it's not very pretty. The reason is that most locations have their Mar 31 counts in the 0-2K range, except 2 locations which have the counts in the 60K range. Edit the right axis to see if you can suit it to your needs. Also, try the single join clause that I suggested to see if you get a nicer dashboard.

                   

                  Hope this helps - try my ideas out and let me know if you have more questions.

                  • 7. Re: Display 2 sets of dates as part of a single chart
                    Neil Powling

                    Hi Stoyko

                     

                    Thanks for this - I've been battering away at this in my spare moments.

                     

                    You are absolutely right - the two dates are not linked at all. What is linked are the authorities. The point is to overlay the data to see if there is a causal link (i.e. poor inspections = increase in Children in Care over time).

                     

                    E.g. poor is pection in 2006 = 3-year increase in children subsequently, followed by good inspection in 2009 after which children in care decreased.

                     

                    This is what led me to my original query design in the first post, and the questions about nulls\cartesian products in the second post - the point is I'm trying to construct a timeline for each individual authority and then plot what happens on different dates over the top.

                     

                    Does this help?

                     

                    Neil

                    • 8. Re: Display 2 sets of dates as part of a single chart
                      Stoyko Kostov

                      Hi Neil,

                       

                      Yes, your problem makes perfect sense. That's why in my most recent post I proposed that you join on location only, and use the union for your date axis.

                       

                      I thought a bit about how I can mitigate the problem of the heterogeneous axes (No CLA at 31 Mar), causing most of the lines to appear flat near 0. Here's my idea - duplicated sheets and different filters on each.

                       

                      Start by dragging the "No CLA at 31 Mar" measure to filters. Choose Range, and "At least 20,000". This will leave only the whole of ENGLAND on the sheet.

                      Duplicate your sheet, and in the copy edit the filter. Choose Range, and choose limits of 10,000 to 20,000.

                      Duplicate again, and in the newest copy edit the filter to have limits of 5,000 to 10,000.

                      Repeat with following ranges for the next steps: 2,000 to 5,000; 1,000 to 2,000; 500 to 1,000; 0 to 500.

                      Of course, feel free to split in other ways that suit your needs.

                      These filters have a problem which we need to address: Score (group) disappeared from the charts.

                      To address, on each sheet drag GeogUnion to the filter area. It will have the values pre-selected according to the first filter. Just click Apply. Now remove the first filter, leaving just GeogUnion there. Score (group) will reappear where defined.

                       

                      You can now combine all sheets in a dashboard, if you prefer.

                       

                      Hope this gives a better view. I would still recommend that you post the workbook with just one join. If you are having difficulties building the viz on the one-join workbook, I can help you with that once I have it.

                       

                      I'm attaching my latest result. Let me know if there is anything you would like to improve in it.