A few points of clarification:
1. What is the x-axis?
2. Is this a one time chart, or would it be connected to a dynamic source data (this isn't necessarily a problem, but some reshaping might be required and this helps guide my process)?
3. Is there a reason ovals are used or would rectangles (i.e. Gantt bars) work just as well?
4. Are the widths intentionally, or accidentally different? If it is intentional, what does it represent?
5. What is color showing?
6. Could you provide a sample of the data this would be based on?
My first thought would be to do a full join with your data and a unit circle, then transform the base circle based on the data values, but I'd need more information to be sure that approach would make sense.
Thanks for your response!
1. Water depth
2. one time chart
3. I would prefer bars, not really sure why I used ovals for the example.
4. The width can be the count of the time (total volume)
5. color is linked to company
6. Data below:
Rated Water Depth Company Design Count 300 CP CP1 1 400 CP CP2 1 300 D D1 4 400 D D2 1 375 F F1 2 350 F F1 2 400 F F2 25 350 F F2 3 400 F F3 2 400 F F4 2 300 F F5 1 350 G G1 2 400 G G2 4 375 G G2 8 350 G G2 8 220 G G2 1 400 G G3 5 375 G G4 2 450 G G5 2 492 G G6 3 460 G G6 2 492 G G7 1 574 G G8 1 400 K K1 16 350 K K1 3 262 K K1 1 400 K K2 1 300 K K2 1 500 K K3 1 400 K K4 2 400 K K5 1 400 K K6 5 400 C C1 2 375 C C2 1 350 C C2 16 340 C C2 2 200 C C2 2 400 P P1 13 400 Z Z1 2
Thanks for your help!
1. Should have been, what is the y-Axis? Is it just discrete? i.e. records sorted by typical irking depth.
6. Your data doesn't contain typical working depth. Is that a function of maximum?
Should each record be a bar, or should they be aggregated further?
7. Is Design used anywhere in the viz?
8. If this is a mockup, what sort of data source are you connecting to?
I think it shouldn't be too hard, but will involve a bit of reshaping.
1. the Y-axis is volume (count of design). It should be discrete.
6. No typical depth, the same designs can range in depth. I can deal with an aggregated view at the company level (then drill down further to the design level if I have to)
7. Not yet
8. I'm currently importing it from an excel table, eventually live connection to an Access database.
Thanks for you input.
This is amazing, great work! Yes, a walkthrough would be helpful.
1 of 1 people found this helpful
No problem. I've got a meeting in a couple minutes, so I'll lead with a high level version. I created 5 duplicate copies of the dataset. These are labeled via [Copy]. You could do this via a full unqualified outer join on a table with the numbers 1-5 in a field labeled Copy (I just did it in excel in this case). I'll help with this join later, it shouldn't be too bad (would be easier with just about any other database though)
A copy is used to make each corner of the rectangles as polygons, so 4 copies, plus a 5th for labels which I used a dual-dual axis to create (though it occurs to me now one dual could have done the job just as well.
Oh! Gotta go.
Thanks Noah, that is extremely helpful. I truly appreciate your time on this!
No problem, I'm happy to help when I can and how can I make this questions are among my favorites.
I don't seem to have access on this machine. I was a bit surprised by this, but I guess I shouldn't be because I don't really use it very often anyway. I believe the same driver is used for Access as for text or excel files, so I tested a query with a couple text files. Let me know if this doesn't work for you and I can try on a different computer when I'm in the office tomorrow.
You'll need to create a second table in your access database with just 1 column and 5 rows with the numbers 1 through 5. Actually any distinct values would do, but these numbers will correspond with the attached workbook so those will be simplest. Now, the JET driver won't want to let you do an outer join or an unqualified one (I believe) but there is a simple workaround to make it happen. Create a dummy field in each table (either directly or in a subquery as I did) and use that for your join criteria. I called my field dummy, again any value would do here, the point is that it be the same on every record in both table. It doesn't actually matter what type of join you do here, since every record is a match, but I did an inner join:
SELECT A.[Company] AS [Company],
A.[Count] AS [Count],
A.[Design] AS [Design],
A.[Rated Water Depth] AS [Rated Water Depth],
B.[Copy] AS [Copy]
(SELECT [OperatingDepth#csv].[Company] AS [Company],
[OperatingDepth#csv].[Count] AS [Count],
[OperatingDepth#csv].[Design] AS [Design],
[OperatingDepth#csv].[Rated Water Depth] AS [Rated Water Depth],
1 AS DUMMY
FROM [OperatingDepth#csv]) as A
(SELECT [Copy#csv].[Copy] AS [Copy],
1 AS DUMMY
FROM [Copy#csv]) as B
Note, this join looks more complicated then it really is. The two sub queries in blue are only there to add a dummy column with 1 for every record. Then these tables are combined so that you get a row for every match between the two tables, and that is every possible combination (i.e. a full cartesian product). So you get a copy of your original table for each number in the copy table.
With these 5 copies, we are now in the case I built by hand for the initial prototype. We want to use 4 copies to build the rectangles and 1 for the labels.
When I'm creating a polygon or line plot, I often make a quick sketch so I'm sure I get my calculations right. For a polygon, you just want to be sure you're going around the perimeter in order otherwise you can end up with weird bow-tie shapes. A rectangle was simple enough that I didn't find it necessary here, but it would be a good exercise to look at the calculations for X and Y coordinates and work through the picture. Note, the min around copy is a token aggregation, it was required because I used aggregation elsewhere in the calc but with [Copy] present on each marks card (path for the polygons and level of detail for the labels) the minimum value is just the same as the value itself. I multiplied by 1.0 in the X calculation because it was coming through as an integer value while the X(label) is a float, multiplying by 1.0 forces a type conversion so the axes could be synchronized.
I just did a standard dual axis in this version (the dual-dual being un-necessarily complex as I mentioned before). The case statement will be null where not defined do X only exists for copies 1-4 and X(label) only exists for copy 5. Y can do double duty, so there is no need to use a dual axis for this. If the indicator that there are null values shows up, you can hide it, these nulls are there for a reason.
Please let me know if you have any problems or if there is anything I left out of my explanation that you don't understand.
OperatingDepth2.twbx 18.6 MB
I can't thank you enough for the time and effort you put into this. Your explanation and examples have been a great help. I'll play with it a little more as I recreate it. If I have any quesitons I'll post them up.
I've never recieved this type of help from a forum before. It's nice to know there are great people out there.
No problem Dylan. I'm glad I could help. Feel free to ping me if you get stuck on anything.