Subtracting grouped sum from previous sum 3

[BigC72]

Currently my Clinic collects printer page counts from all our our network printers. This count comes from a SQL table as a text field. I have the conversion set up so that I can sum the fields. On top of that the data is grouped by date and time stamp. So the total is a sum of a converted grouped text field. Since this number from the printers is just a running total I would like to get the actual page count per week.

Is there anyway to subtract the previous weeks grouped sum from the most current weeks to give me that "actual" week page count? Thanks

 

[Madawc]

You could do a pair of running totals, one for this week and the other for last week. Then do a formual field to find the differece.

It helps to give your Crystal version, since newer versions have extra options, and some extra problems. I use Crystal 8.5.

Madawc Williams (East Anglia, UK)

 

[BigC72]

Sorry about that, I also am using Crystal 8.5. I'm pretty new to Crystal and am teaching myself as I go so some of these things are foriegn to me initially. I'll give your suggestion a try. Thanks

 

[Madawc]

If you're new, watch out for Null values. When writing formulas, note that any test for null must always come first. Without isnull, Crystal will stop processing the formula when it hits a null value; the default assumption being that null means show nothing. I made this mistake several times while I was learning Crystal, because it's not obvious to someone who's used to other programming languages.

Madawc Williams (East Anglia, UK)

 

[BigC72]

I've done the running total but I'm unsure of how to separate out this weeks total against last weeks since it's totalling the same group each time. There is no "previous" command to indicate current week - previous week. At least that I've come across...

 

[lbass]

If you really only want the figure for the last full week, then, using the running total expert, insert a running total where you select {table.amt}, sum, evaluate based on a formula:

{table.date} in lastfullweek

Reset never.

Or, if you want the last 7 days, change the formula to:

{table.date} in last7days

Substitute your date field for {table.date}.

-LB

 

[BigC72]

LB thanks for the new information it works perfectly as well.

I appologize I guess I'm not posing my question in the proper manner. The figures as they come off of the printers are never zeroed so last week the total page count may have been 10,000,000. Well that figure is a figure from the beginning of time. Let's say this weeks page count is 10,050,000. I'm attempting to determine if there is a way to subtract the count that is essentially one week old from that that is current. In essence showing me that my page count for that week was 50,000. Since they're all coming from the same summed group I'm in way over my head.

Thanks for all the help to this point...

 

[lbass]

There are two problems here. One is that you keep referring to page counts. We don't have a clue what's on your page and it might or might not correspond to one group only. If you have the pages set up so that there is a new group before and a group always fits on one page, then we should be talking about group counts, not page counts. If the page count is really what you mean, then the groups are probably irrelevant.

Secondly, you say that my suggestion worked perfectly, and yet it doesn't seem to be what you are looking for. My guess is that you want a subtotal that reflects each week's (or page's total) rather than the accumulating total or just the last week's total. Either one is probably easy enough to help you with, but we need to know exactly what you are looking for.

The most helpful thing would be for you to supply some sample data, showing your groupings and some detail data and what you would like to show for your results. Please try to clarify the group vs page issue, as well.

-LB

 

[BigC72]

LB,

Each week a script runs that pulls a count of the total number of pages that each of our network printer has printed up to that point in time since it was put into production(essentially our printers odometer). This count grows as you would expect it to each week. I am pulling these cummulative counts for each printer from a SQL table where they are stored exactly as indicated. This is done for every printer at the same time each week. Unfortunately the data is in the SQL table as a text field so it must first be converted in order to be summed. I currently have the report grouped by a Time and date stamp that each record is given at the time the script runs. Essentially the grouping is by the day of the week (Sunday) in which the polling is done. The first date of the report is the first date of polling done this year where the total number of pages was say for example 5,550,000 pages well as the year has gone on that total number has grown to 7,500,000. Growing a by approximately 50,000 or so prints each week. The only way I can think to find the weekly print total is to take the most recent total and subtract the previous weeks total from it. However since these totals come from that same grouped sum I cannot determine how to do this or if it is possible. I hope this is a better description of my problem.

I am not a Crystal guru, I am just starting me learning process with it and sometimes may not know how to phrase my questions properly. LB I told you that your suggestion worked because it taught me a different way to accomplish part of my task, one that I did not know before but I was still in need of further help. Sorry if I've thrown to many curve balls here...

 

[lbass]

From what you say, I think your data looks like this:

start
printer1 300
printer2 200
printer3 400
subtotal 900

week1
printer1 500
printer2 300
printer3 700
subtotal 1500

week2
printer1 700
printer2 500
printer3 900
subtotal 2100

where each printer page count is a hard value in your database, but in string form, reflecting a running total as new amounts are added from week to week. The subtotals are then inserted summaries (sums) on your converted count field. The converted count field {@printeramt} looks something like:

tonumber({table.stringcount})

I think you can calculate weekly amounts by creating three formulas:

//{@prevweek} to be placed in the detail section:
whileprintingrecords;
numbervar prevweek := prevweek + {@printeramt};

//{@reset} to be placed in the weekly date group header_b section:
whileprintingrecords;
numbervar prevweek := 0;

To insert the group header_b section, right click on the gray area to the left of the report canvas where it says GH1 and choose "insert section below."

//{@currweek} to be placed in the group header_a section:
whileprintingrecords;
numbervar prevweek;
numbervar currweek;

currweek := sum({@printeramt},{Orders.Order Date},"weekly")
-prevweek;

This will only work if you place {@currweek} formula in the group header_a section, since it is a running total carrying forward the previous week's value.

-LB

 

[BigC72]

Thanks LB, you hit it out of the park. Thanks for hanging in there with me and working through my question.