7 Replies Latest reply on Sep 1, 2016 10:39 PM by laguna92651

    Charting Multiple Tables With Inconsistent X Axis

    laguna92651

      I have 5 tables (a table for each of 5 companies), that I want to use to plot specific financial data for all 5 companies on one plot (i.e. Inventory Turns etc.) in column and/or line charts by year.The problem is the data in the charts have holes in them and the data series don't all start and stop with the same year.

       

      I am assuming I need a temp table for each of the five tables that have a common year range to all of the company tables and where there are holes enter a 0. I would then use the list function with the delimited data source in the chart. Is this the simplest approach? I can't seem to figure out how to place the data in the correct "cell" once I create temp records and enter the year range. The range of years is only going to be 5 or 6 years. I've looked at the Array Chart articles from Filemaker Hacks, but it seems a little more than I need and can't seem to get started with it?

        • 1. Re: Charting Multiple Tables With Inconsistent X Axis
          jbrown

          Howdy.

          You are pretty much right when you talk about a temp table with records for the holes of data, the 0s.

           

          The graphs all need the same starting point and need the same amount of records.

          You can use a summary table: something that holds static records of the sales for each month of each year for each company regardless of the numbers. A script would run end-o'-month to collect the sales and place it in this table.

           

          You could also use a virtual list.

           

          For summary table would generate quicker; the virtual list would be a bit of a delay in performance.

           

          Check out this post: Dynamic Charting in FileMaker - Soliant Consulting . It contains the summary table I used to generate the chart.

          • 2. Re: Charting Multiple Tables With Inconsistent X Axis
            laguna92651

            That's funny, I was just looking at your example this morning and had it open as I was reading your post. I will go back and look at it. I did have a couple of question on it. I'm assuming your running the CreateScript manually, couldn't find any script triggers or buttons that ran it. I didn't see how the 0's were entered. I sort of have my app running, one of my graphs isn't starting at the correct year, but the next data point is correct, but just starting to try to figure out where I goofed up. I'm using the List function, should that work okay? Thanks for your help.

            • 3. Re: Charting Multiple Tables With Inconsistent X Axis
              jbrown

               

              Sorry about that. The "Create Script" was just putting in random numbers for the data.

               

              YOu would write a script that goes to each company and finds the total sales for each month. You'd simply sum the sales.

              I'd have to see how your file is set up. could you provide a screenshot or two of your company layout and your relationship graph?

               

              I'll try to modify my file to be more like what you're looking.

              jb

              • 4. Re: Charting Multiple Tables With Inconsistent X Axis
                laguna92651

                I not even doing any summing of data. I am just entering financial data into individual portals for each table. Then calculation some ratios, for example Receivable Days is calculated and to be plotted.

                Portal Input.png

                Map Chart.pngThe

                The T01s and T01u, in the center are the T0's for the tables for the entry portals. The T01s1 and T01u1 are the T0's for charting. They are linked by Year 1 to T01s and T01u. I run a script that creates a predetermined number of year records, then populates the record using a the auto entry with an if statement, to either give me the Receivable Days or a 0 if field is empty.

                 

                Data1.png

                Data3.png

                The data is populated correctly in the temp Chart tables, the Receivables Turns has not been updated yet.

                Recv Days Chart.png

                The chart is wrong, uses x-axis from Company 1 T0. Company 1 is correct, Company 3 is incorrect. It seems that the correct graph depends on which x-axis is chosen to use for the set of graphs. I'm using List () to plot the data.

                Recv Days Chart3.png

                In this image, Company 1 is wrong, Company 3 is correct. Chart is using x-axis from Company 3.

                • 5. Re: Charting Multiple Tables With Inconsistent X Axis
                  laguna92651

                  Hi Jeremy

                  Any luck to get this to chart properly? Thanks

                  • 6. Re: Charting Multiple Tables With Inconsistent X Axis
                    jbrown

                    Good evening. I haven't yet but can get you something in my morning.

                     

                    Talk soon

                     

                    jb

                    • 7. Re: Charting Multiple Tables With Inconsistent X Axis
                      laguna92651

                      Okay, thanks much Jeremy.