Question about conditional Formulas 1

[RobNauticus]

Hello everybody,

I am attempting to create several formulas that summarize parts of my report (avg). I have one formula that seems to be working averaging over all of group1. The second formula does not seem to work that is the same formula with an additional criteria limiting the average to only those records that are marked as true in the database.

I am using CR10 and MS Access 2003

Here is the formula that I think works:

if {Weekly_Data.date_WeekOf} in [currentdate to currentdate - 30]
then Average ({Weekly_Data.int_cur_Sales}, {Market.int_MarketID})

This one takes all the records in a 30 day period and averages them per group1.

if {Weekly_Data.date_WeekOf} in [currentdate to currentdate - 30] and {Company_Division.yn_Lennar} = true
then Average ({Weekly_Data.int_cur_Sales}, {Market.int_MarketID})

This formula does the same calculation but it should only average records that are marked as "true" within the 30 day period.

I have checked all of my relationships and fields that are associated and everything looks fine.

If anyone has suggestions I would love to hear them!

Thanks for looking,
Rob

 

[synapsevampire]

First, you need to be the one that confirms whether your formulas work, I'm guessing neither works.

Is this output just part of the overal report requirements?

You're better served to supply example data and the expected output for the report.

To limit rows in the report to the last 30 days, use Report->Selection formula->record and place:

{Weekly_Data.date_WeekOf} in [currentdate to currentdate - 30]

The difficulty with your attempt is that it looks at the current row ONLY (you don't bother to state WHERE youare using this formula) and takes the action.

If you switch to using a Running Total, you can do this and select Evaluate Use a Formula and place your criteria in there, then select for each group.

-k

 

[RobNauticus]

Thanks for the reply,

I checked both formulas and they are not working correctly. This output is only a small part of the report that summarizes the more detailed sections below. I cannot limit the report to the last 30 days because there will be a separate formula for 60 and 90 days as well. I am placing the formula in the GROUP HEADER 1 on the report.

I have just tried the running total idea but I am having the same result which is nothing.

To illustrate the desired output see sample below.

My data looks something like this:

Thanks for the help...

Rob

 

[synapsevampire]

You can't place the formulas in GH1, put them in the group footer.

Now to get averages for part of the data in a group header you'll need to manually do this.

A formula for the amount:

if {table.date} in currentdate to currentdate -27 then
{table.amount}
else
0

A formula for the count:

if {table.date} in currentdate to currentdate -27 then
1
else
0

Now reference these formulas in the group header as:

sum({@MySumFormula},{table.group})/sum({@MyCountFormula},{table.group})

-k

 

[RobNauticus]

Thanks for the help! I am implementing it now...

It looks like it will work for me.

Thanks again