AnsweredAssumed Answered

Comparing 2 Years of Records Stored in Same Table

Question asked by AllegroDataSolutions on Feb 5, 2016
Latest reply on Feb 8, 2016 by 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.