5 Replies Latest reply on Apr 8, 2015 6:25 AM by philmodjunk

    2 Line Chart, repeating date issue?

    SkylerJohnson

      Title

      2 Line Chart, repeating date issue?

      Post

      I'm attempting to make a 2-Line Chart that tracks the following data (simplified for troubleshooting)

      The DATE and AMOUNT that two Entities have been paid. 

      Here is my current table -- 

      As you can see, both entities get paid on the same day. But when trying to create a 2-line chart, I get this:

       

      As you can see, the dates are doubled, and I would like them to be on the same line. I also included the calculation that i used for each Y-Series. 

      What should I do? Thank you!!!

        • 1. Re: 2 Line Chart, repeating date issue?
          philmodjunk

          The type of chart set up you are using would work if you had one record for each date and different fields within that single record for your two entity values.

          You can experiment with a different data source option such as one that is used for "groups of data when sorted", but I suspect that you'll then need to define your Y-Series calculations as calculation fields and define two different summary fields to use for your y-series data.

          It might also be possible to use ExecuteSQL expressions for your data series and then use the delimited data option in data sources.

          • 2. Re: 2 Line Chart, repeating date issue?
            SkylerJohnson

            @PhilModJunk

            The type of chart set up you are using would work if you had one record for each date and different fields within that single record for your two entity values.

            The issue I would have with this is there needs to be expandability for more than 2 Entity values in the future. Is it possible that I need to break this 1 table into multiple tables instead? Possibly a table for "EventDates" that only contains a date field?

            You can experiment with a different data source option such as one that is used for "groups of data when sorted", but I suspect that you'll then need to define your Y-Series calculations as calculation fields and define two different summary fields to use for your y-series data.

            You lost me on this one...one thing I tried was swapping between "Summarized groups of records" and "Individual record data", when my chart data is is "Current Found Set", and I attached the result to this post. As you can see, it appears to only chart the FIRST record related to each day, not BOTH (this just frustrates me to no end...haha). I believe this has to do with where the charts are placed on layouts AND the sort order, but I can't get it to chart both lines of data in this way. 

            It might also be possible to use ExecuteSQL expressions for your data series and then use the delimited data option in data sources.

            This is WAY over my head cool

            I'm thinking I need to rethink how to setup a database to display this type of data...I might have to experiment with creating separate tables for dates

             

            • 3. Re: 2 Line Chart, repeating date issue?
              philmodjunk

              Separate tables won't help here. I wasn't suggesting that you change the design of your table, BTW, just pointing out why your set up as described in your first post wasn't working.

              When using the summarized data option, you also have to use summary fields for the Y series.

              Add a calculation field such as: If ( Entity = "entity 1" ; Amount )

              Then define a summary field that computes the total of this calculation.Specify the summary field as your Y series value

              Add another calculation field and corresponding summary field for the second entity.

              But your best option really is delimited data using ExecuteSQL and the queries needed would not be complex.

              Your first Y series calculation for Entity 1 would be:

              ExecuteSQL ( "Select Amount FROM YourTableNameHere WHERE Entity = 'Entity 1' " ; "" ; ¶ )

              The X-series would be:

              ExecuteSQL ( "SELECT DISTINCT Distribution_Date FROM YourTableNameHere Order By Distribution_Date " ; "" ; ¶ )

              Note: field and table names in FileMaker often cause syntax errors in SQL queries due to either being a reserved word or containing characters that are not allowed for names in SQL. This issue is solved by enclosing the names in double quotes like this:

              "SELECT DISTINCT \"Distribution_Date\"

              • 4. Re: 2 Line Chart, repeating date issue?
                SkylerJohnson

                @ PhilModJunk, thank you! I will be testing this out tomorrow and will let you know the results. Really appreciate the help smileyyes

                • 5. Re: 2 Line Chart, repeating date issue?
                  philmodjunk

                  Hmmm on reviewing my last post, I recommend including:

                  Order By Distribution_Date

                  at the end of each of the Y-Series queries just to make sure that the listed values are in the same order as the dates listed for the X-Series.