monthly weight difference calculation 1

[fisheromacse]

Hello and thanks in advance.
I thought i had this figured out before the holiday but must have eaten too many rolls and have dough for brains now.

I am using Crystal xi and MS SQL database.

I have been asked to create a report that lists the weights for individuals, and compares the first weight from each month to determine if there is a significant weight change.

There can be as many as 3 weights per day (93 per month) per person, or as few as 0 weights per month per person.
I am only interested in the 1st weight entered each month.

The output should list the names and monthly weights for the previous 60 days.

DESIRED SAMPLE OUTPUT:
Name Wt Date Weight WeightDif SigChange
Sample, B 11/4/2009 209 0
Sample, B 12/12/2009 275 69 1
Test, A 10/31/2009 100 0
Test, A 11/23/2009 101 1 0
Test, A 12/2/2998 188 87 1

ACTUAL SAMPLE OUTPUT:
Name Wt Date Weight WeightDif SigChange
Sample, B 11/4/2009 209 0
Sample, B 12/12/2009 275 9 1
Test, A 10/31/2009 100 0
Test, A 11/23/2009 101 1 0
Test, A 12/2/2998 188 -33 1

It all seems to be working except for the WeightDif calculation. And i think the problem is how i am using {@previouswt}. it appears to be comparing to the most recent previous, not the previous group/month weight, thus providing a measure that is not accurate for the request (ie: if there was a weight today, yesterday and Nov 30, it is comparing today's to yesterday's and i need it to compare today's with Nov 30's.)

Suggestions?


Currently I have the following:
Group Header 1: {@LastFirstName}
Group Header 2: table.CheckDate

There are no items in the details section.

Group Header 1, details and all Group Footers are suppressed.
In Group Header 2 i have the following:
tablename.checkdate
tablename.checkby
{@previouswt}
(@currentwt}
{@perc}
{@weightdif}
{@sigchange}

FORMULA DEFINITIONS:
{@lastfirstname}
table.lastname&", "&table.firstname;

{@currentwt}
stringvar tnc;
numbervar tnn;
if isnumeric({medchecks.QRESULTS})
THEN tnn := (tonumber({medchecks.QRESULTS}))
ELSE tnn := 900000;
IF tnn = 900000
THEN tnc := "Entry Error"
ELSE tnc := totext(tnn);
tnc;

{@previouswt}
stringvar tnp;
numbervar tpn;
if isnumeric(previous({medchecks.QRESULTS}))
THEN tpn := (tonumber(previous({medchecks.QRESULTS})))
ELSE tpn := 900000;
IF tpn = 900000
THEN tnp := "Entry Error"
ELSE tnp := totext(tpn);
tnp;

{@perc}
IF ({CLIENT.CID}=(previous({CLIENT.CID})))
THEN
((tonumber({@currentwt})/tonumber({@previouswt}))-1)*100
ELSE 900000;

{@sigchange}
numbervar sigch;
IF {@perc}<890000
then
(IF {@perc}<-4.99999999
OR
{@perc}>4.99999999
THEN sigch:=1
ELSE sigch:=0);

{@weighttdif}
numbervar pr;
numbervar wd;
IF isnumeric({@previouswt})
then pr := tonumber({@previouswt})
ELSE pr := 0;
IF isnumeric({@currentwt})
and isnumeric({@previouswt})
and {CLIENT.CID}=(previous({CLIENT.CID}))
then
wd :=tonumber({@currentwt})- pr
ELSE wd := 99999;
wd;


Selection Criteria:
({table.TypeCheck} like "*weight*") and
({table.checkdate} in Aged0To30Days
OR {table.checkdate} in Aged31to60Days)

 

[lbass]

Can we assume your date group is set on change of month?

If so, you could create a formula like this for the difference. It must be placed in the group #2 header for month:

whileprintingrecords;
numbervar curr;
numbervar prev := curr;
numbervar curr := {table.weight};
curr-prev

You should add a reset formula in the Group #1 header:

whileprintingrecords;
numbervar curr := 0;
numbervar prev := 0;

Instead of converting your numbers to strings, I would just use the display string formula area to return the error messages.

-LB

 

[fisheromacse]

yes, the date group is set on change of month. I tried to include all needed info, and, of course, forgot something that obvious.

Thank you.

it seems so simple seeing what you did i am almost embarrassed to have overlooked it.

Thanks again!