comparing values across months 1

[fisheromacse]

I have what i think should be a straightforward report, but am having a mental block as to how to accomplish it.

I am pulling a list of clients and their monthly weights.
I need to compare the weights of each client over a year.
For example, find the amount of weight change and the % of weight changes from: last month to this month, this month compared to 3 months previous, this month compared to 6 months previous, this month compared to 9 months previous and this month compared to 12 months previous.

I am stuck on the month to month portion.

I cannot use previous record for the client, for although all clients are required to have monthly weights, some clients have daily weights others weekly. I need to compare only the 1st weights of each month.

My report is structured as follows:
GROUP HEADER 1 : Residence (suppressed)
GROUP HEADER 2 : FullName (suppressed)
GROUP HEADER 3 : CheckDate (ascending, for each month)
: Group Header 3 contains the formulas CheckDate, CheckBy, and Weight
Details : all suppressed
All GROUP FOOTERS are suppressed

Formulas:
//@CheckDate
stringvar cd := totext({table.checkdate});
Date(val(left(cd,4)),val(mid(cd,5,2)),val(right<cd,2)))

//@CheckBy
IF isnull({table.checkby}) then "No Entry"
else {table.checkby}

//@Weight
IF isnull({Table.value}) or not(isnumeric({table.value})) then 900000
else tonumber({table.value})

There will be a few other formulas that i do not have completed yet:
//@WtChange - calculate the number of pounds changed
//@PctChange - calculate the percentage of weight changed
//@SigChange - determine if % change for 1 month is greater than 5%


Here is a sample of the detail data:
Client CheckDate CheckBy Weight
Bob Bob 01/01/2010 Sue Sue 104.7
Bob Bob 01/22/2010 Mark Sue 105.3
Bob Bob 02/02/2010 Sam Sam 112.1
Bob Bob 03/03/2010 Mark Sue 108.3
Bob Bob 03/23/2010 Sue Sue 114.8
Bob Bob 03/30/2010 Sam Sam 107.4
Bob Bob 04/04/2010 Sue Sue 105.1
Bob Bob 04/16/2010 Sam Sam 105.7
Bob Bob 05/11/2010 Mark Sue 109.9

I have the formulas in the group header, so it displays the correct values, but when i attempt to find the difference between the weights, I get stuck.
I tried:
\\@PrevWt
IF {table.ID}=previous({table.ID}) and (isnumeric(previous({table.value}) and isnumeric({table.value})) then tonumber({table.value})-tonumber(previous({table.value}))

but as expected it uses the previous record, not necessarily the record displayed in the group header.

Here is how i would like the data above to display, and would have formulas for monthly weight change (current - previous), % of monthly change (((current/previous)-1)*100), and if change was significant(>5% for 1 month) to the right of Weight:

Client CheckDate CheckBy Weight
Bob Bob 01/01/2010 Sue Sue 104.7
Bob Bob 02/02/2010 Sam Sam 112.1
Bob Bob 03/03/2010 Mark Sue 108.3
Bob Bob 04/04/2010 Sue Sue 105.1
Bob Bob 05/11/2010 Mark Sue 109.9


If i forgot any information, please prompt me for it.

thanks in advance.

 

[lbass]

I would start by eliminating the unnecessary records from the report. Create a SQL expression {%minmo} like this:

(
select min (`date`)
from table A
where {fn month(A.`date`)} = {fn month(table.`date`)} and
{fn year(A.`date`)} = {fn year(table.`date`)} and
A.`client` = table.`client`
)

The punctuation and functions would be specific to your datasource, so adjust as necessary. Then add a record selection formula to your report:

{table.date} = {%minmo}

This should return results as you show at the end of your post in the detail section if you group by client. Then you could create conditional formulas like this:

//{@currentwgt}:
if {table.date} in dateserial(year(currentdate),month(currentdate),1) to currentdate then
{table.weight}

//{@threemonthsago):
if {table.date} in dateserial(year(currentdate),month(currentdate)-3,1) to dateserial(year(currentdate),month(currentdate)-2,1)-1 then
{table.weight}

Then you could compare the current weight to three months ago by using a formula like this:

(sum({@currentwgt},{table.client})- sum({@threemonthsago},{Table.client}))% sum({@threemonthsago},{Table.client})

-LB

 

[fisheromacse]

lb,
THANKS!
I was just not thinking of using a SQL expression at all!
it is looking much better now!!

 

[fisheromacse]

lb,
so very close.
What i need is more of a rolling weight change.

your formula //{@threemonthsago) was not quite what i needed, but got me thinking.

What i am starting to do (and please let me know if this is a poor plan) is crate a formula for each month like this:
//@JanWt
numbervar janwt;
IF {table.Date} in dateserial(Year({table.date}),1,1) to dateserial(year({table.date}),1,31)
and isnumeric({table.weight})
and {table.ID}=next({table.ID})
then janwt := tonumber({table.weight})
else
(if {table.ID}=previous({table.ID})
then janwt := janwt
else janwt := 0;
janwt


I then plan to create formulas for 3, 6 and 9 months ago using Select Case statement to calculate the weight differences, like:
//@Weight3Ago
numbervar mono := month({table.date});
select mono
case 1: if {@WtOct}<>0 then tonumber({table.weight})-{@WtOct}
case 2: if {@WtNov})<>0 then tonumber({table.weight})-{@WtNov}
etc
etc for the remaining months

any suggestions? potential downfalls?

thanks again!

 

[lbass]

Why do you feel the need to use variables? In what way didn't my suggestion work? There should be only one value per client, so it should work as I showed. I don't know what your ID field refers. If it is a client ID, there shouldn't be more than one row per client, so previous and next IDs should always be different. Or do you have some other table that is causing repeating values? If so, use maximum instead of sum.

-LB

 

[fisheromacse]

LB,
it is probably something i am missing, but when i use your formula, i get blanks for all rows except the one that is 3 months before the current date. For example, in the sample below, if i were to add the @threemonthsago formula to the right of the Weight column, none of the records would have a value displayed.

Client CheckDate CheckBy Weight
Bob Bob 01/01/2010 Sue Sue 104.7
Bob Bob 02/02/2010 Sam Sam 112.1
Bob Bob 03/03/2010 Mark Sue 108.3
Bob Bob 04/04/2010 Sue Sue 105.1
Bob Bob 05/11/2010 Mark Sue 109.9


I am using the variable so that the values will remain until the next matching month, or new client.
I placed them in a hidden sub-section of Group Header (GH3b). In this way, i can show FH3b and view the monthly weights for the previous 12 month period.
I then use the formulas to determine the next columns (1 month weight change & percentage, 3 month weight change and percentage, etc).


An example of the desired output with a second client's data added is below.

Client CheckDate CheckBy Weight # Changed1 # Changed3 #Changed6
Bob Bob 01/01/2010 Sue Sue 104.7
Bob Bob 02/02/2010 Sam Sam 112.1 7.4
Bob Bob 03/03/2010 Mark Sue 108.3 3.8
Bob Bob 04/04/2010 Sue Sue 105.1 -3.2 0.4
Bob Bob 05/11/2010 Mark Sue 109.9 4.8 -2.2
Bob Bob 06/07/2010 Sam Sam 109.1 -0.8 0.8
Bob Bob 07/01/2010 Sue Sue 108.5 -0.6 3.4 3.8

Jane Dave 01/05/2010 Sue Sue 88.3
Jane Dave 02/06/2010 Mark Sue 89.4 1.1
Jane Dave 04/03/2010 Sam Sam 90.1 0.7 1.8
Jane Dave 05/04/2010 Sue Sue 90.3 0.2 2.0
Jane Dave 07/01/2010 Sue Sue 89.3 -1.0 -0.8 1.0


If a person does not have a weight for a month, the previous month's weight is used to calculate the weight difference for the #Changed1, but the month # is used to calculate the #Changed3 and #Changed6.
In the above example, Bob's data is perfect. Jane's data, however is not. There were months when she was not weighed. Using July as the example month, there was no June weight to the 1 month weight change uses the next present previous month, May in this case. But for the 3 and 6 month weight changes, they must be calendar months apart, so if Jane did not have data for April or July then the 3 or 6 month does not fall back onto another value.

If i were to show the hidden GH3b, it would look like this:

Client CheckDate CheckBy Weight # Changed1 # Changed3 #Changed6
Bob Bob 01/01/2010 Sue Sue 104.7
104.7 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
Bob Bob 02/02/2010 Sam Sam 112.1 7.4
104.7 112.1 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
Bob Bob 03/03/2010 Mark Sue 108.3 3.8
104.7 112.1 108.3 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
Bob Bob 04/04/2010 Sue Sue 105.1 -3.2 0.4
104.7 112.1 108.3 105.1 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
Bob Bob 05/11/2010 Mark Sue 109.9 4.8 -2.2
104.7 112.1 108.3 105.1 109.9 0.0 0.0 0.0 0.0 0.0 0.0 0.0
Bob Bob 06/07/2010 Sam Sam 109.1 -0.8 0.8
104.7 112.1 108.3 105.1 109.9 109.1 0.0 0.0 0.0 0.0 0.0 0.0
Bob Bob 07/01/2010 Sue Sue 108.5 -0.6 3.4 3.8
104.7 112.1 108.3 105.1 109.9 109.1 108.5 0.0 0.0 0.0 0.0 0.0

Jane Dave 02/01/2009 Sam Sam 78.3
0.0 78.3 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
Jane Dave 01/05/2010 Sue Sue 88.3 10.0
88.3 78.3 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
Jane Dave 02/06/2010 Mark Sue 89.4 1.1
88.3 89.4 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
Jane Dave 04/03/2010 Sam Sam 90.1 0.7 1.8
88.3 89.4 0.0 90.1 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
Jane Dave 05/04/2010 Sue Sue 90.3 0.2 2.0
88.3 89.4 0.0 90.1 90.3 0.0 0.0 0.0 0.0 0.0 0.0 0.0
Jane Dave 07/01/2010 Sue Sue 89.3 -1.0 -0.8 1.0
88.3 89.4 0.0 90.1 90.3 0.0 89.3 0.0 0.0 0.0 0.0 0.0


It is Friday and the end of a very long week and the start of a very busy weekend, so it is utterly likely that i am taking something and making it way too complex.

 

[lbass]

Okay, I see where you are going with this. I'm not sure if there is a question in here or not. I wouldn't have built the reset into the formula itself, but instead would have added it in the client group header--just to keep the formulas simpler.

-LB

 

[fisheromacse]

i am not sure if there is a question any longer.

The suggestion to use a SQL expression was spot on for getting my pointed to the correct data set!

I like the idea to move the variable resets to their own formula and put it in the group header.

I have been asked to make the report for the 13 previous months, but also have been warned that it will probably be wanted for both a 25 month period and a lifetime history, so the more flexible the better!

I am definitely open to additional suggestions.

Thanks again.