10 Replies Latest reply on Apr 2, 2013 1:38 AM by FredrikWallenberg5579

    Comparing data of several years in one graph

    PecCars

      Title

      Comparing data of several years in one graph

      Post

      I am migrating an MS Access 2003 app to FM Pro 11. A substantial portion of the app are graphs that compare data over several years based on a date field Inquiry::InquiryDate.

      Basically it will compare the count of records based in Inquiry::InquiryDate by month for 3 years on a rolling basis (see uploaded screen shot here: http://cl.ly/1F1r1r2h3R0Z1y070S1C). In MS Access all I need to do is create an on-the-fly calculated field for year, have the counts at the vertical axis, months at the horizontal axis and via 3D clustered column select 3 years per month entry.

      So far I found out the following for FM:

      - In FM I will create a calculated field in Inquiry so that it calculates the first day of the month, I will also need to create another calculated field for the year.

      - I assume that on-the-fly calculated fields are not possible in FM as opposed to adding them as calculated fields to a table, so I will need to add more fields to the Inquiry table (lots of clutter considering that this goes on for several date fields additional to .

      With this I could generate the graph by month for one single year. Now how to we show several years in the same graph (not stacked but side-by-side).

      - As far as I understand FM does not offer a side by side bar chart. How would I compare monthly data for several years in one single chart ?

      Any ideas how to do this in FM Pro 11 ?

      Graph_stats_using_a_date_field_in_MS_Access.JPG

        • 1. Re: Comparing data of several years in one graph
          philmodjunk

          FileMaker charts are still in their infancy being a brand new feature with the latest release. It is a common experience to hit limitations with what you can do with them.

          If you can structure a utility table wher you have 12 records, one for each month of the year and global fields for each year, you can define a series of relationships (think queries in Access) that each match by a different year and thus you can define calculations that compute totals for each specified month and year--with each different yearly total for January found in the first record, the yearly totals for February found in the second table and so on.

          I agree that's a cumbersome approach, but it does work. (And you can define the calculations for this in the chart set up rather than in fields in the table if you want to.)

          • 2. Re: Comparing data of several years in one graph
            PecCars

            mmmhhh ... that sounds scary ... lots of relationships need to be managed ! What I am doing now is similar but believe a bit simpler. I have created one single utility table for all months and years. I could then populate the total fields per month and year, either by (hopefully) a simple script or by calculation fields (not sure how this works ... apparently I can summarize with GetSummary data into one field ... but then can I do a find using date ranges to filter the records that I need, before calling GetSummary ?).

            As far as the charts are concerned, it seems that the easiest will be to buy a plug-in. So far I found FusionCharts and FusionMaps which seems to be a good combination of functionality and low cost.

            • 3. Re: Comparing data of several years in one graph
              philmodjunk

              To compare 5 years of totals you'd need 5 relationships only. That doesn't sound to me like an unmanageable number of relationships to use for this purpose.

              The reason I've suggested this is that it's a way to link to different sets of record by month and year in separate fields (or at least separate calculations) for each year for that month so that the user simply selects a year or range of years one time and the 12 'month' records all update to point to the needed records for summary purposes.

              • 4. Re: Comparing data of several years in one graph
                PecCars

                That sounds interesting. The way I understand your suggestion is to create a summary table per year, 12 records ... 1 per month. Or do you mean one year-"neutral" utility table with only summary data.

                Quote: you can define a series of relationships (think queries in Access) that  each match by a different year and thus you can define calculations that  compute totals for each specified month and year

                Not sure I understand how do the relationships between tables and global variables would work. Can you elaborate please ? Maybe with a simple example ?

                • 5. Re: Comparing data of several years in one graph
                  philmodjunk

                  Yep, one "year neutral" set of 12 records.

                  You can use unstored fields such as a global field in relationships much like any other field as long as you respect the fact that the resulting relationship only works from the table with the unstored key to the table that does not have an unstored key.

                  Thus you can have a "year" global field such as gYear1 that is just a number field. You can create a series of calculation fields cYear2, cYear3... defined as gYear1 + 1, gYear1 + 2 and so forth.

                  A number field, monthNumber, can hold a number from 1 to 12 for the 12 records.

                  Now you can define a series of unstored key fields for your date based relationships:
                  cDateKey1: Date ( MonthNumber, 1, gYear1)
                  cDateKey2: Date ( MonthNumber, 1, cYear2)
                  and so forth.

                  This type of relationship can then be defined for the first "year":

                  UtilityTable::cDateKey1 = MainTable::cPurchaseDateMonth  
                  (cPurchaseDateMonth is a calculation field to return the first of the month from the purchase date.)

                  You'd continue the pattern for each additional month and year you'd want to compare in your chart.

                  Now you can define calculations with aggregate functions such as Sum, count, average... etc. in your chart's axis definition to get totals for charting purposes.

                  • 6. Re: Comparing data of several years in one graph
                    PecCars

                    Phil,

                    on your first post of this thread you state:

                    "If  you can structure a utility table wher you have 12 records, one for  each month of the year and global fields for each year, you can define a  series of relationships (think queries in Access) that each match by a  different year and thus you can define calculations that compute totals  for each specified month and year--with each different yearly total for  January found in the first record, the yearly totals for February found  in the second table and so on"

                    Can you please elaborate on the "series of relationships ... by ... year". I am now able to summarize by month and year correctly the data and even chart it. My question is now how to I break it by year ... and by month in the same data set.

                    Lesson learned by me so far: Use the table mode to generate the table that you require for your chart. Once that's done ... generate the chart !

                    • 7. Re: Comparing data of several years in one graph
                      philmodjunk

                      Define these number fields with global storage: gYear1, gYear2, gYear3, gYear4....

                      or

                      gYear 1 as global number field and cYear2 as gYear1 + 1, cYear2 as gYear1 + 2 ....

                      Define matching calculation fields to combine a month with the year:

                      cMonth1: Date ( Month ; 1 ; gYear )
                      cMonth2: Date ( Month ; 1 ; gYear2 )
                      and so forth

                      Define a series of relationships:

                      ChartTable::cMonth1 = DataTableM1::cMonth
                      ChartTable::cMonth2 = DatatableM2::cMonth
                      and so forth
                      DataTableM1 and DataTableM2 are both table occurrences of the same data source table. cMonth is defined as: DateField - Day(Datefield) + 1 which computes all dates to be the first of the month for whatever month that date falls in to match back to the other cMonth fields on the other side of these relationships.

                      Sometimes a better thought comes later, I'm thinking this may work better with ChartTable records with a single year field, a single cMonth calculation field and just one relationship by cMonth to the data field. You could perfrom finds on the ChartTable to pull up the series of records you want for the chart and you could sort them first by month, then by year to cluster them by year as you've specified in the example in your first post.

                      • 8. Re: Comparing data of several years in one graph
                        FredrikWallenberg5579

                             I went a different route ...

                             I created a calculated field for the sales by year. I.e. Sales2012: if (cYear = 2012, Sales, 0) and same for Sales 2011 etc. I then create summary fields for the same and use those summary fields as data series in my graph where I have cMonth on the x-axis. Since the calculations are unstored I figure I don't loose too much in storage efficiency.

                        • 9. Re: Comparing data of several years in one graph
                          JamesTorr

                               Fredrik - sorry to reopen a dormant thread but I'm trying to do exactly what you've just suggested. 

                               Could you break what you've done down into more intermediate level steps without wasting too much of your time!

                               Thanks very much in advance!

                               James

                          • 10. Re: Comparing data of several years in one graph
                            FredrikWallenberg5579

                                  1) Make a separate table for the summary. It has a remote Key in common with my main sales database (in our case that is the close time of the ticket. It is unique in our POS system).

                                 2) I set up a relationship between the two tables so that I could reference the sales from the main table rather than import a copy, but that isn't necessary.

                                 3) You need a month field in the reporting table (at least I wasn't able to use a remote field for the xlabel). I use the year field from the main sales database through the relation.

                                 4) Set up one calculated field per year in your reporting table: 

                                          Sales2011 = If (DinerWareSales::cYear = 2011; DinerWareSales::NetSales ; 0)

                                 5) Set up a summary field (total) for each SalesYear field

                                 6) Create the graph with the xaxis:

                                         MonthName ( Date ( MonthlySales::Month ; 1 ; 2010 ) )

                                 The calculation is to get the month name. You could do it in other ways too (Choose for example) but I felt this was pretty easy. It would be tempted to just use MonthName(CloseTime), but that doesn't seem to work.

                                 7) Set up one y-series for each summaryYear field.

                                 8) To display the graph you need to sort the data on month then year. I have a script for that on "LayoutEnter".

                                 9) Finally, to update the display table I delete all and then import my sales records (CloseTime).

                                 See if that works for you.