I spent the last 3 days (slow learner), getting aggregates in a portal to work. As this is being built as a mostly iPhone app, after completing the first layout ( 8 portals with one calculation in each ), I downloaded the app to my iPhone to check on the speed. Everything works as it should, except that it takes 15 seconds to load the layout (3 seconds on the desktop).
The sales table has 17,000 records. Each portal (8 on this layout) is filtered to show only invoice dates for a certain year and only invoices that are for product (not freight or GST). Some portals additionally only show partial years based on the current month (Year to date sales).
I have a summary field "s_TotalAmount" that is the total of the "InvoiceAmount" field. I then have a calculation "cu_TotalAmountM_n" that takes the summary field and divides by 1,000,000. I then insert this merge field into each portal, followed by "M". I use the calculation field so as not to take up as much space on the iPhone. $3.3M takes up a lot less space than $3,300,000. The calculation field is automatically an unstored calculation because it's based on a summary field.
As I said, everything works, but with just one layout completed, I can see that it's going to be too slow to be usable.
My thought is to create another table that holds the totals for each month for each dealer. I would only keep the last three years, meaning 36 fields in this table. CurrentYearMonth1, CurrentYearMonth2, and so on. As I only update the database once per month, everything could be refreshed at that point, before being distributed.
Before I go this route, I was hoping someone here might have a suggestion or insight?