I'm trying to generate a graph in FMP 14 and am struggling with it. Here's the set up:
I have 9200 patient records. Each record has lots of information but all we care about is the insurance product they have (HMO or PPO), the month and year of their service (this is a text field data element I created: Month ( Admission Date ) & "/" & Year ( Admission Date ) ) and the patients service category (there's a total of five options, each patient has been given one.)
Below is an example of the graph I'm trying to reproduce in FMP. It uses the exact same data (exported from FMP and was a 45 second pivot chart I whipped together). What I'd like to have FMP produce is two graphs that look identical to to the one below. One is for HMO product. The other is for PPO product. The x-axis is the month & year of service. The y-axis is the count of patients by each of the categories.
I'm finding this to be obtusely difficult in FMP. I've created sub-summary reports with scripts that sort records by product, then category, then date, then service category. There's a sub-summary section for each of these sort criteria. When I put field elements into the report, each sub-summary totals like I would expect but the graphs are useless garbage. I've looked as much as I can and am now resorting to asking for help because I just don't have time to mess around trying to figure this out any more.
Lastly, I'm using FMP because even though this can be done very simply and quickly in Excel I
(a) want to understand how to do it in FMP and
(b) want to show others how awesome FMP is and they should be glad they got the licenses.
Thank you in advance for your help.