Bar chart with two y-axis using related tables
I have been trying to create a bar chart that shows data from 2 related tables on the y axis and shared labels used to sort both tables along the x axis. I have tried everything I can think of.
I would like to show the total number of inspections for each project 'type' (this is the field used to sort and summarize the data in both tables) and the total approval duration summed by each project 'type'. The total number of inspections for each type I have calculated using a calculation field 'get summary' in the table 'inspections'. The total duration of approval summed by type I have calculated in the table 'projects' using a calculation field 'getsummary'. The two tables are related by the field 'project name'.
Example 1 is what the chart looks like when I have just 1 y-axis (the approval duration by type). The data is correctly showing here
And example 3 is what it looks like when I add the # of inspections by type (from the related table 'inspections'). the data does not show up.
Example 4 is what it looks like when I chart only the total inspections by type. The data shows up correctly
When I try to add the approval duration data from the related table 'projects' as another y axis to this chart is doesn't work either. I am using current found set, the sort order of the layout is 'type' and I am summarizing groups of records.
Why can't I display both y axes at once? Both fields are calculations using the 'get summary' function. and the tables are related and sorted the same. The only way I can get both field to show up on the same chart is by selecting 'related records' choosing the related table, and selecting 'type' for the sort order. But then only one type shows up (in example 5 it's gas plants...but got cut off)
I have had to comment a bunch below since I can't figure out how to get the pasted images in here to show up.