Incorrect Sum

[JCruz063]

Hi All,

I'm having a problem with a Crystal Report of mine. I'm working with Crystal Reports 10 on a System with Windows 2000.

The object of my report is to print a daily average of data for a series of items on a weekly basis per month, per state, and per time period, on a page. When my report is printed, it will display rows and columns. Each row will be made up of each item whose daily average (for each week, for the whole month, per state and per time period) I want to see. There will be four 5 major columns in the report (one for each week of the month). Those columns will be further divided into 2, one for the average of the item, and the other for the percentage (out of the whole month) that such average represents. Thus, each page of my report should look something like this:

[tt]
Average Weekly Data
January 2005
New York
10:00 AM - 12:00 PM

Week 1 Week 2 etc
No. % No. % No. %

Item 1 22 5 -- -- -- --
Item 2 5 3 -- -- -- --
Item 3 etc
Item 4
------------------------------------------------
Total 50 100 -- -- -- --[/tt]

Obviosly, every time period must print on a different page, as must every state, and every month.

With the above little graphic in mind, I set up 3 groups for my report. Group 1 groups the records by month; group 2 groups by state, and group 3 groups by time period. I have 2 additional groups below these ones but they're used to to group the items together (as each item may have different criteria), and to insert blank rows within the items.

Ok, good so far.

The average that I'm printing is the total number of incidents of each item in the week, divided by the number of days in the week. Thus, if my first week has 5 days (monday - friday), and Item 1 appeared 10 times, my daily average for Item 1 in week 1 would be 2. The number of days in my weeks, however is not always the same because they depend on how many days worth of data I have. For example, the third week of January has 7 days in it. My database, though, includes data only for the first two days of that week, and so my average will be based on 2 days, not 7. That's where my problem comes in.

Here's my problem: I need to compute how many days worth of data there are for each particular week. To do this, I created 5 arrays (one for each week) and 5 formulas (also, one for each week). Each array stores the different unique dates in each week. In my formulas, I check the date of the current record, and if it's not found in my array, I redim preserve the array and add the new date to it. Of course, this is done, only when the date in question belongs to the week represented by the formula. I return one when this is the case, and zero otherwise. This way, I keep a count of how many days there are in each week. The formulas for each week look like this:

global datevar array arrDatesInWeek1;

local numbervar thisWeek := 1;
local numbervar weekIdx;
local numbervar returnVal := 0;

if not (({theDate} in arrDatesInWeek1)) then
(        
  // total days in the week
  weekIdx := WeekOfMonth({theDate}, crMonday);
    
  if weekIdx = thisWeek then
  (    
    returnVal := 1;            
    redim preserve arrDatesInWeek1[ubound
      (arrDatesInWeek1) + 1];
    arrDatesInWeek1[ubound(arrDatesInWeek1)] :=
      {theDate};
  );
);
returnVal;

As you can see, this formulas return either a zero or a one depending on whether the date in question belongs to the week represented in the formula. WeekOfMonth is a custom function I created which simply returns a number from 1 to 5 based on a given date. It works beatutifully, so when looking at the code assume the formula works correctly.

To compute how many days there are in the week, I do the following:

[tt]Sum({@WkXDayCount}, {@TimeGrouping})[/tt], where @WkXDayCount is the formula that I use [above] to compute the total days in each week. There are 5 of these formulas so I have Wk1DayCount, Wk2DayCount, etc. @TimeGrouping is the formula used to group the records by their time range. The time grouping formula has only one line in it: It looks like this:

[tt]{startTime} & " - " & {endTime}[/tt]

I'm using [tt]Sum({@WkXDayCount}, {@TimeGrouping})[/tt] because I'm interested in the total number of days worth of data for that particular time period in the state and month.

So, at last, here's the what my problem is. It happens to be that [tt]Sum({@WkXDayCount}, {@TimeGrouping})[/tt] is returning the wrong number of days for time periods after the first one. More precisely, [tt]Sum({@WkXDayCount}, {@TimeGrouping})[/tt] is returning less number of days for time periods after the first one. If I only select records containing one time period, the report prints out correctly. As soon as I select records containing more than one time period, I get the correct number of days for the first time period in the first month and first state, but as soon as the time period changes, I get less days than what I'm supposed to get per week. Can anyone suggest what the problem may be?

I have tried to change the whileredingrecords and whileprintingrecords statements in my formulas to no avail. I have whilereadingrecords on the WkXDayCount formulas, and I have whileprintingrecords on the formulas that I use the number of days to compute the average. Oh, and one last thing: The arrays containing the number of days in each week is declared in a formula that I placed in the header of group 1 - the one that groups by month. I also redim the arrays back to 1 so that they are cleared out for every month.

Any thoughts?

Thanks!

JC

_________________________________________________
To get the best response to a question, read faq222-2244.

 

[smcnulty2000]

When you redim arrDatesInWeek1, how do you remove the first date that it has stuffed inside of it?

scottm.

 

[JCruz063]

scottm,
Thanks for your reply!

According to the documentation and help files, [blue]redim[/blue] erases the array and fills its new elements with default values. Thus, the [blue]arrDatesInWeek1[/blue] is propperly cleared at the heading of each group1. Evidence that the array is being cleared properly is the fact that I get fewer days that I'm supposed to get.

The problem lies, I believe in the timing of the report. I mean, the formulas that calculate the days per week are marked as 'whilereadingrecords', and the formulas that print the days per week are marked as 'whileprintingrecords'. This makes perfect sense. However, because the records can only be read once, my arrDatesInWeek1 is only able to store one set of dates and thus, calculating the days per week for each time period would not be possible. At least that's my theory. What do you think?

Thanks!

JC

_________________________________________________
To get the best response to a question, read faq222-2244.

 

[Naith]

I'm not sure you should be using WhileReadingRecords anywhere in these formulas at all.

Have you already tried using WhilePrintingRecords across the board? What was the impact?

Naith

 

[JCruz063]

Naith,
Thanks for your response!

I need to use [blue]WhileReadingRecords[/blue] because I wouldn't be able to do any data calculation otherwise.

JC

_________________________________________________
To get the best response to a question, read faq222-2244.

 

[Naith]

I'm not sure I'm with you.

You can perform data calculations using WhilePrintingRecords, as long as the formula assessing days per week features before the formula using the days per week variable.

Naith

 

[smcnulty2000]

I was actually looking at this part of your code:

if not (({theDate} in arrDatesInWeek1)) then

Not as an error but with a question in my mind that this is what is shortening your data set programmatically, so it is the likely culprit.

Let me put it another way. You say you are resetting the array at the beginning of the month group, which is your first grouping level. You have as a subgroup of this the time period; 10a - 12p. You ask for multiple time periods, so you have the month, the array sets itself to 1 element with a null value in it.

You go through your first time period, all is well.

Next you get a second period of time, 12p - 2p. But you haven't reset your array, so if something comes along that has that date your if-then skips it. If it happens to fall outside your previous dates, then it counts it.

So you end up with less of them, and increasingly declining.

At a guess your numbers drop dramatically at each time period so that by the time you got to your last time period you would have nearly zero or all zeroes coming out. But that would depend on your data.

Timing issues (whileprintingrecords, etc) tend to go for the all-or-nothing results. Losing pieces isn't really symptomatic of that.

I realize given the complexity of your question that I may not have fully understood what is going on. But what do you think?

scottm.

 

[JCruz063]

Thanks for your replies guys! Sorry for my late response.

Next you get a second period of time, 12p - 2p. But you haven't reset your array, so if something comes along that has that date your if-then skips it. If it happens to fall outside your previous dates, then it counts it.

scottm,
I also thought that may have been the problem, but then again, for testing purposes, I'm resetting the dates on the timeperiod group and that seems to have no effect at all on the outcome. Also, the number of days in each week don't continuosly drop. They go up and down with no apparent pattern. How about this: Just by looking at the report sample I wrote on my first thread, how would you envision creating a report that would print something like that?

Thanks!

JC

_________________________________________________
To get the best response to a question, read faq222-2244.

 

[smcnulty2000]

Weird problem. I'm going to have to ruminate on this some more.

Did you realize that by most people's standards Jan 2005 had six weeks, counting partials? Does this matter to your solution?

I think I would have approached this from the standpoint of making the grouping like this:

Groups

State
-Month
--Time Period
---Item
----Week
-----Day
------Detail record.

Then plugged formulas in and tried to use the "underlay following section" under section expert after plugging in the hand-written summary formulas for each piece.


Scottm.

 

[smcnulty2000]

Let me take that back. I think there's a better way to handle it.

You set up a series of formulas that look like this:

if weekofthemonth({Sheet1_.Date})=0 then 1 else 0
all the way to

if weekofthemonth({Sheet1_.Date})=5 then 1 else 0

You put these in your detail section. Under their respective columns.

Then you write summary formulas to sum these.

Sum ({@Week01PresentOrNot}, {@Groups04Item})

Creating summary formulas for each column.

You plug those into your item level (Group level 4 if you went with the schema I used in the previous post).

From there you should be able to do the rest.

Ought to work.

Scottm.

 

[lbass]

If you want the average to be based on the dates in the week in your data (not the week itself), then I think you could do this:

Insert groups on {table.date}, print for each month, {table.city}, and {@time} (your time interval). I assume you have a formula for {@weekofmonth} that looks something like:

Datepart("ww",{table.date})+1
- Datepart("ww",({table.date} - Day({table.date})+1))

Create formulas for each week like the following for {@week1}:

if {@weekofmonth} = 1 then 1

Then create these formulas:

//{@reset} to be placed in the timeinterval group header:
whileprintingrecords;
stringvar x1 := "";
numbervar y1 := 0;
numbervar avewk1 := 0;
stringvar x2 := "";
numbervar y2 := 0;
numbervar avewk2 := 0;
|
V //etc.
stringvar x6 := "";
numbervar y6 := 0;
numbervar avewk6 := 0;

//{@accumwk1} to be placed in the detail section:

whileprintingrecords;
stringvar x1;
numbervar y1;
stringvar x2;
numbervar y2;//etc.

if {@weekofmonth} = 1 and
instr(x1,totext({@date},"MM/dd/yy")) = 0 then
(x1 := x1 + totext({@date},"MM/dd/yy")+", ";
y1 := y1 + 1);
if {@weekofmonth} = 2 and
instr(x2,totext({@date},"MM/dd/yy")) = 0 then
(x2 := x2 + totext({@date},"MM/dd/yy")+", ";
y2 := y2 + 1); //etc. up to week 6

Then add a separate display formula for each average to be placed in the time interval group footer, like this one for week 1:

//{@displavewk1}:
whileprintingrecords;
stringvar x1;
numbervar y1;
numbervar avewk1;

if y1 = 0 then 0 else
avewk1 := sum({@week1},{@time},"by AMPM")/y1

Then drag the time interval groupname into the group footer and suppress the details and group header. Of course, if you are then accumulating at other levels, you need to add additional variables that are not reset at the time interval group level.

-LB

 

[JCruz063]

Guys, thank you all for your replies. I'm having Crystal Reports technical support work on this issue. I'll let you guys know how it goes.

Thanks again.

JC

_________________________________________________
To get the best response to a question, read faq222-2244.

 

[smcnulty2000]

Let us know if they have a solution, if you get the opportunity.

scottm.