1 Reply Latest reply on Feb 8, 2016 6:20 AM by AllegroDataSolutions

    Comparing 2 Years of Records Stored in Same Table

    AllegroDataSolutions

      I am working on a solution where the client wanted a chart, a bar graph showing sales from the current and previous years. No problems with that. I set up two global fields, which are populated by a startup script: ThisYear.g and LastYear.g. The first date is calculated using the Get(CurrentDate) function. The calculation for the second field simply subtracts 1 year from the first. The chart appears to be populating correctly.

       

      The problem is the client has lots of layouts where they need to see portals -- or calculated fields arranged like a spreadsheet -- that compare sales figures from the current month to the totals of the same month last year. Basically, they want 2 or more data series on the layout. Complicating the matter somewhat is that they all have to be filtered by vendor and product codes.

       

      So, to use a very simplified example, when the user is viewing the record for a particular product, he would see  columns showing the ...

       

      Name of Vendor 1, Sales for the product to Date for the Current Quarter, Sales for the Same Period Last Year, and the Difference between the two figures.

       

      Name of Vendor 2, Sales for the same product to Date for the Current Quarter, Sales for the Same Period Last Year, and the Difference between the two figures.

       

      ... and so on. With the bottom line being totals of each column. The sales figures come from a related table, which contains every line item from their invoices. So the figures in each row (except for the last one, with the column totals) are basically summary values. (Usually totals, but some will be averages and minimums.)

       

       

      First, I tried this with a portal and calculated fields for the bottom line totals, but I couldn't get consistently accurate results. Since there are only 8 vendors, I tried to do the whole thing with calculations. So, for example, for the first field (which will contain the sales to date in the current quarter of this year) I put a calc in the related table that showed the amount of the sale if the line item if the seller was identified as Vendor 1. Then I added a summary field, to get a total for all the rows for Vendor 1. Finally, I put a calculation on the parent record that uses a relationship that matches the current quarter in the current year to the records in the child table and gets the summary figure. The results showed the same amount for both years. Since there were no sales for this item in the previous year, the total for last year should have been zero. I tired using the vendor ID as the break field for the summary in the parent table. This resulted in a total of zero for both years.

       

      What am I missing here? Does what they want me to do only work with charts? I'd appreciated any ideas.

       

      Thanks.

        • 1. Re: Comparing 2 Years of Records Stored in Same Table
          AllegroDataSolutions

          Okay, I have a little more information about the problem above. Some of the same issues are surfacing when trying to obtain the same statistics via reports. The odd thing is, after shutting FMA 14 down and restarting it, the report versions correctly calculated the sub-summaries. Furthermore, some (but not all) of the chart data the calculations I used to generate the same results shown in the reports also worked.

           

          My best guess, at this point, is the FMA needed time (or, perhaps I should say, a trigger) to generate the indexes used to sort the data. If this is the case, it's a royal pain. It means that any unusually complicated report or calculation will take much longer to build, because I will have to keep closing and reopening the file before I can determine whether the code worked.

           

          I have been asking FMI for YEARS to include a command (and a script step) to force indexes and other resources to rebuild. Also, to restore the command to compact the database. So much unnecessary aggravation could be avoided with a few simple tools.