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!
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.
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.
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.
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.
Children and Inspections_1.twbx 94.8 KB
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?
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.
Children and Inspections_1.twbx 119.5 KB