10 Replies Latest reply on Jun 20, 2012 8:25 PM by Philosofy

    Creating a rolling twelve month report

    Philosofy

      Title

      Creating a rolling twelve month report

      Post

      I'm brand new to Filemaker Pro.  My company supplies me my sales data in either an Excel or .csv format.  I've figured out how FMP can import the file, but I'm having problems.  Each month from the last 5 years has its own column for both the quantitiy I sold and the dollar value.  I would like to organize my data in a rolling twelve month report: the column headings would be:

      Customer

      Manufacturer of Product

      Product Name

      Product Package Size

      Month

       

      Under each month would be a cell for the lbs sold, and one for the $ amount, with totals for each customer.  

       

      I would like the report to only include the last twelve months data.  Is there a way to do that?

       

      Thanks!

       

      Phil

        • 1. Re: Creating a rolling twelve month report
          philmodjunk

          Unfortunately, the structure of your Excel or CSV file does not lend itself to making this as easy to do if each row in the file was a different month.

          You'll need a way to get the data into a table where you have one record for each cell in your columns of monthly data as seen in Excel. Then you can set up filtered portals such that you select a month/year in a global field and that set's up what data appears in the one portal with each of 11 additional portals then computing a portal filter that is one month different from that specified month and year to get columns of data for each month of your 12 month time frame.

          You can use a variety of methods to get the data in that structure. You can import repeatedly, importing one month's data into the same field of new records each time you import, You can import all the data at once into an interim table, then use a script that loops through fields and records to split up the data into new records in the table you need for your report--just to name two approaches that might work.

          • 2. Re: Creating a rolling twelve month report
            Philosofy

            I'm not sure I was clear (or I don't understand.)  Each row has the data I want: its just that there are so many columns (5 yrs * 12 months * (data values)= 120 columns.).   Are you saying each row should only have one piece of data in it associated with each product?

            • 3. Re: Creating a rolling twelve month report
              philmodjunk

              Yep, if you want to organize sub totals in each column by month and control exactly which month's data appears in each column, then the monthly data needs to be broken up in to separate records. The exact details are something that I can't fully spell out without know a bit more about that spread sheet and the desired format of your report.

              I get it that each column represents a different month of the year.

              What does each row in your spreadsheet represent?

              What should each row in your report represent?

              How are the monthly columns labeled in the import file? are they always a 12 columns with January in the first column or do these "roll" just like you want in your report?

              What we are talking about here is often called a "cross tab" report and you can research that independently if you wish.

              • 4. Re: Creating a rolling twelve month report
                Philosofy

                OK, I think I see what you are saying.  I'll try playing with the program that gives us the spreadsheet, and research cross tab reports.  I was able to do the report I wanted about 15 years ago with Access, so I know I can do it now, I'm just very rusty.

                • 5. Re: Creating a rolling twelve month report
                  philmodjunk

                  The method used to create a cross tab report in Access will be quite different from the approach used in FileMaker.

                  • 6. Re: Creating a rolling twelve month report
                    Philosofy

                    OK, I got back to this, did some work, and hit another snag.  I was able to export the data so each sale is a separate row in Excel, so it exports nicely to a .csv file.  But here is the problem: the date does not import properly.  I have two choices, and I will take the easiest solution:

                     

                    I can import the Invoice Date, but it comes in the format yyyymmdd, so the date appears as 20120605 for June 5 of 2012.  Filemaker can convert ddmmyyy, but I can't find a way to convert what I have.  However, there might be another solution.  I can also import two other data fields: month and year.  They import into different fields, with the month being January, February, etc, and the year is 4 digits: 2012, 2011, etc.  

                    Does anyone have a suggestion for me?  I have to get this thing done in the next two weeks.

                    Thanks everyone.

                    Phil

                    • 7. Re: Creating a rolling twelve month report
                      philmodjunk

                      I will call the field with yyyymmdd imported into it ImportDate in this expression It can be a field of type number or text:

                      Date ( Middle ( ImportDate ; 5 ; 2 ) ; Right ( ImportDate ; 2 ) ; Left ( importDate ; 4 ) )

                      Will convert your imported date into a FileMaker date. You can even set up ImportDate as a global field, and then specify a field of type date with the above expression as an auto-enter calculation (Learned this trick from LaRetta). You then have to enable auot-enter options during import, but this will put a converted date into your date field as part of the impoart process. (There's a check box for this option that appears in the final little dialog that appears just before you initiate the import.)

                      There are ways for converting the month name and year into a date, but since that leaves out the day, I think the first option is a better choice.

                      • 8. Re: Creating a rolling twelve month report
                        Philosofy

                        Thanks.  I had to have someone translate where to put that, but I'll work on it tonight.

                        • 9. Re: Creating a rolling twelve month report
                          Philosofy

                          Got it!  Thank you!

                          • 10. Re: Creating a rolling twelve month report
                            Philosofy

                            I'm still having problems.  I read FMP for Dummies, but I must be even dumber than that.  Here's the two snags I have:

                            1.  I will be importing my data every week from the external source.  Its basically all the old data plus the new data from that week.  I'd hate to spend a lot of time setting up FMP, only to have to start all over again the next week.  Is there a way FMP can look to a .csv file for its data?  I can then replace that .csv file every week with the new data.

                             

                            2.  I still can't figure out how to group my records by month.  I've included a rough idea of what I'm looking for in an Excel screenshot.  I'll want it to look much nicer, but you can get the gist of it from that screenshot.

                             

                            Thanks for any suggestions you might have.