I was tasked with creating a chart with the following information. How would you recommend displaying this in one chart? Is it possible?
Parent: Clients table (contains Assigned AM field, status=active) - Child: Products table (contains current profit, current revenue, current # of products, status=paused or cancelled) - Child: Products History table (contains historical info (same fields as Products table)
Do you have something today that is doing this outside of Quickbase you're trying to recreate? At face value I would say that maybe, you could put this in a single report but fundamentally I wouldn't suggest you not do it.
The primary reason is just your Y-Axis is probably going to be all over the place, specifically for any $ values that will far outpace the # of clients and products, but even if you do 2-different y-axis you're still likely to have a wide range between whatever series you choose being clients and products, and secondarily I'm not sure even if you solve for that that you could get it to properly summarize that data by AM while reporting on time.
You could try and get creative with a line and bar chart where you would provide your profit and revenue as the primary y-axis (line chart) and then put the clients / products on the x-axis (bar chart). The only consideration here is that you would need to isolate the unique clients/products in order to have them properly display as a bar chart. You would not be able to put everything as a line provided that you can only do a sum/average aggregation on charts. This probably only solves part of it though in that it is a holistic view of the org and not broken out by AM.
My suggestion would be to flip this around and make it a suite of reports on a single dashboard. You can line up the 3 reports on the same time frame and color scheme for each AM and sort order and then stack them on top of each other to conceptualize the pattern.
This may help with the comparison to the prior period.
Super Easy Year Over Year Chart by Month
In terms of displaying those 3 different metrics, I would suggest a dashboard (like Chayce described) with 3 reports and a filter by the AM as needed. Or if you have an account manager table, you could add the 3 reports to their record (no relationship to the other tables needed just a report link).