7 Replies Latest reply on Feb 29, 2012 5:55 AM by nkolios

    New to Charting

    nkolios

      Hi, I'm a bit new to charting in Filemaker. I have a table which contains a list of product sales showing volumes of sales only. The list is extensive and covers several years, the products are grouped together by type. I am trying to compare volume sales per month, so for one year I will have 12 bars, but also compare the years so each month could have several series representing the different years. At the moment they are altogether in one table and I wanted to know if there was anyway of producing this chart without have to break my data up.

       

      I have added a field to each record which puts each record into a particular year and our financial year starts at the begining of October so i have also had to reshuffle the months by assiging a number to each month.

       

      Any help would be appreciated.

       

      Thanks

        • 1. Re: New to Charting
          Mike_Mitchell

          Hello, nkolios.

           

          I'm not 100% sure from your description what data you have available, but if you have month and year available on each sales record, one way this can typically be done is to have a separate table that lists month and year, then set up a relationship between that table and the raw sales data. Then, use a calculation that summarizes the sales data for the related data by month and year. For example:

           

          SalesByMonth = Sum ( Sales::Volume )

           

          Then, you can chart SalesByMonth from the Months table.

           

          HTH

           

          Mike

          • 2. Re: New to Charting
            LyndsayHowarth

            Hi nkolios,

             

            I have attached an image which shows such a feature within a single sales-data table. I have tried to obscure data which makes it recognisable so a lot of the categories or Types (subsummary) are missing... and I've blurred the actual numbers...

             

            The date periods (global fields) are generated by entering one and the rest are auto-populated according to a pattern the user selects from a popup with options for months, quarters and years.

            Each column is a relationship by category and time period.

             

            Works well... but I have not tried charting it. I shouldn't imagine it would be too much of a problem...

            PeriodComparison.jpg

             

            - Lyndsay

            • 3. Re: New to Charting
              nkolios

              Hi Lyndsay,

              Thanks for this.  I will certainly give it a go.

               

              Niko

              • 4. Re: New to Charting
                nkolios

                Hi Mike,

                I was going to try to method I had writien a case statement to give each year a number and then through a self join I could separate the data and chart it that way.  I was just having trouble with my casr statement, its just not doing what I am expecting it to do.

                 

                Case(Date ≥ 1/10/2009 and  Date ≤ 30/9/2010;1;

                        Date ≥ 1/10/2010 and  Date ≤ 30/9/2011;2;

                        Date ≥ 1/10/2011 and  Date ≤ 30/9/2012;3;

                )

                 

                This is what I had come up with, but it does not give me what I need.

                 

                Thanks

                 

                Niko

                • 5. Re: New to Charting
                  Mike_Mitchell

                  Hey, Niko.

                   

                  Dates in FileMaker are stored as numbers, so you'll need to parse your text-based dates into actual date values using the GetAsDate ( ) or Date ( ) functions. For example:

                   

                  Case ( DateField >= Date ( 10 ; 1 ; 2009 ) and DateField <= Date ( 9 ; 30 ; 2010 ) ; 1 ;

                             DateField >= Date ( 10 ; 1 ; 2010 ) and DateField <= Date ( 9 ; 30 ; 2011 ) ; 2 ;

                             DateField >= Date ( 10 ; 1 ; 2011 ) and DateField <= Date ( 9 ; 30 ; 2012 ) ; 3 )

                   

                  Also, watch out for that trailing semicolon on the end of your function. It's probably going to break your syntax.

                   

                  HTH

                   

                  Mike

                  • 6. Re: New to Charting
                    Mike_Mitchell

                    Oh - just a suggestion, but if you have a need to do this sort of thing on a regular basis (make determinations based on a fiscal year), it might be worth building a custom function that does it for you. That way, you can feed the CF a date and year and have it return a 1 or 0 based on whether the date falls within that year. Might cut down on the possibility of errors.

                     

                    Mike

                    • 7. Re: New to Charting
                      nkolios

                      Hi Lyndsay,

                      I would be interested to know how you achieved this.  What I had been trying to do with my data was to create table occurances of my main data table and basically have a table occurance for each year then creating a data set for each year and then charting it but it does not seem to be working and I'm not sure why. I am not able to create the correct set of records I am looking for.

                       

                      graph.jpg