Sum(), Summing & Such ...

[LaRetta]

Hi everyone,

I’ve done something I know NEVER to do. I term it Band-Aid design. It’s similar to urban sprawl, indicating unplanned-for additions on an existing process. I created a major dog, ie, unstored calcs referring to unstored calcs which refer to unstored calcs because info was needed in the exact moment it was requested (know what I mean?) and I couldn’t THINK in that exact moment! I’ve attached my existing process (only two example TOs) and a GIF showing the existing process results. I hate to fess up to this kind of mess but I want it right. I’ve tried several things and I just can’t determine the best approach here. My method works but it's very very slow and ugly.

Purpose: Create several comparisons (based upon 30-day chunks only; no weekend/holiday considerations or month-end issues) to produce estimates of average quantities sold per day in each period. These projections will help determine quantities to order based upon trends and allows us to keep ahead on stock.

Range example: From now back 30 days, total Quantity sold divided by “30” = avg qty sold per day. That result is divided into cAvailToSell then multiplied times today's date to project (from that range) when we would sell out. This cAvailToSell quantity is inventory summary less unshipped LineItems. This repeats with each TO … from now back 60 days, etc, in an inclusive pattern. THEN … find which date of all the TOs is the SOONEST (shown as Minimum in blue in the attached GIF) and display that date. Purpose: Considering all ranges within the last year, how soon COULD we run out. I’ve learned a lot more about Div() and remainders and relationships and such but obviously not enough to come up with a clean, elegant solution. Everything I try produces aggregate unstored calcs of unstored calcs and MANY of them. Ideas would be greatly appreciated!

LaRetta :wink2:

Statistics.zip

Edited December 4, 2005 by Guest
Corrected File

[Fenton]

Sorry, bit of a delay in response. You may have already come up with something. What I did was to take your calculations and convert the process to a scripted method. I don't know if it would actually be faster when it's run. But it would certainly be faster to display afterwards.

It seems to me that they are causing a bit of burden by wanting everything to be dynamic based on the current date, then going back 30 days, 90 days, 6 mo., 1 year. Yes, it is accurate and looks good on a spreadsheet. But it would be so much faster if they'd do something like: this month, previous 3 months, previous 6 months, last 12 months. In that case only "this month" is really dynamic. The others could be fixed, by storing the totals for each month, for each product.

You would need a table to store these totals. To go further, you could have another table to store a summarized total for products sold on each day, with a process to add days. You could export summarized by product and date (probably a text file), then import. Then, after the end of each month, summarize those records by month, into the month totals table.

Yes, it's somewhat redundant data. But it could be used for many types of aggregate reports.

ProductStats_fej.zip

Edited December 10, 2005 by Guest
Added corrected file to match LaRetta's