5 Replies Latest reply on Jun 6, 2017 4:16 PM by erolst

    Basic Charts

    raykennedy

      It's the simple things that seem to trick me up sometimes. Never really did much with charts in FMP.

       

      Just testing out one now where I want to get number of sales shown on y axis in a line chart by month/year on the x axis but want them grouped by month  so it would show in Jan there were 4 sales and March 2 sales etc instead of individually like it is showing in the screen shot.

       

      I attached some screen shots to better illustrate my setup. The problem is that it won't group the dates by month. I did notice one thing in the graph on the x axis when I select date I get a question mark and will only show month and year if I have number selected. The Field it is a date field and these were imported as dates with no errors. Is it possible FMP is still not recognizing it as a date but that would be odd since it is able to decipher Month Name and Year based off that field when calculated.


      I do have this sorted by the date so not sure why it is not working. If anyone recognizes something I did wrong, please feel free to point that out. I got a feeling I am missing something probably pretty obvious.

        • 1. Re: Basic Charts
          siplus

          if you want to group data by month, you should create a key YearMonth combining year and month into a single figure and create relationships that gather data based upon that key.

          • 2. Re: Basic Charts
            raykennedy

            I don't quite understand. Do you mean setup a field that calculates month and year and use that as a x-axis data.

             

            Tried that and it didn't work but may not be understanding what your saying either.

             

            What doesn't make sense to be is I have this exact same setup on an older FMP and it works fine but on this it doesn't.

            • 3. Re: Basic Charts
              erolst

              Create a calc field, type date, as

               

              theDateField - Day ( theDayField ) + 1

               

              Also, create a summary field, type Total of:, targeted at whatever field you want to aggregate.

               

              Find the appropriate records and sort by this field. Without sorting, you won't get properly summarized results.

               

              It (the field) is also the one providing the x-labels. Since it is still a date field, you should be able (I don't chart that often ...) to do some formatting In order to display just the month name, or month name / year.

               

              The summary field gives you the y-values. In the Chart under Source, you need to specify "Current found set (summarized values)" (or the option to that effect, anyway).

              1 of 1 people found this helpful
              • 4. Re: Basic Charts
                raykennedy

                erolst

                 

                Why did that work. Why do I need this....

                erolst wrote:

                theDateField - Day ( theDayField ) + 1

                 

                vs just using something like MonthName (Datefield) or MonthName(Datefield) & " " & Year(Datefield).

                 

                If I am reading that right it is taking the date field subtracting the day and adding it again.

                 

                Just curious what that actual does to make it work with that?

                • 5. Re: Basic Charts
                  erolst

                  raykennedy wrote:

                   

                  erolst wrote:

                  theDateField - Day ( theDayField ) + 1

                   

                  vs just using something like MonthName (Datefield) or MonthName(Datefield) & " " & Year(Datefield).

                  You can do that, but the former doesn't work across year boundaries and doesn't sort properly, as does the the latter. Since you can format a day in the layout any way you want, this give you the best of both worlds - proper sorting and the desired label.

                   

                  raykennedy wrote:

                   

                  If I am reading that right it is taking the date field subtracting the day and adding it again.

                   

                  Just curious what that actual does to make it work with that?

                  Not quite; the formula is subtracting the respective day value of that date, and adding the constant 1.

                   

                  So 6/1/2017 will return 6/1/2017 - but so will 6/6/2017, as well as 6/30/2017 etc. This way, all records from the same month/year will have the same value, and can be grouped together and summarized