Jump to content

Sum(), Summing & Such ...


This topic is 5774 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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:


Edited by Guest
Corrected File
Link to comment
Share on other sites

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.


Edited by Guest
Added corrected file to match LaRetta's
Link to comment
Share on other sites

This topic is 5774 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...

Important Information

By using this site, you agree to our Terms of Use.