Avg of (CurrentDate - DateField) 1

[willz99ta]

Hi,

I am trying to summarize a formula field in my report.

The formula field's code is:
if {tblTESTNewItemRequests.RequestStatus}="Product" then
if count({@1OpenReqNew}) > 0 then
(CurrentDate - cdate({tblTESTNewItemRequests. RequestedDate}))

When I try to put a summary of formula field in the report footer I get the error, "This field cannot be summarized".

Hmmm

Thanks for the help,
Will

 

[IanWaterman]

Sounds like your formula @1OpenReqNew contains a summary or variables.

Whats does it do?

Ian

 

[willz99ta]

It outputs a 1 if a field has a certain value:
if {tblTESTNewItemRequests.RequestStatus} = "New" then 1

 

[willz99ta]

bump

 

[IanWaterman]

You do not have an else.

So if your conditions are not met the formula will not evaluate. You need to add at least one deafault condition.

Ian

 

[lbass]

There are a couple of issues here. First, I doubt whether you really want to sum something only if the total count for the report is greater than 0--wouldn't you be testing this at some group level?

Secondly, you should be using "sum" not count on your formula, since the count of a formula will return the number of times it executes--not the number of time it meets your criterion.

You could accomplish this with a running total. First create a formula {@diff}:

CurrentDate - cdate({tblTESTNewItemRequests. RequestedDate}

Insert the running total and use average of {@diff} and then in the evaluation section, use a formula:

{tblTESTNewItemRequests.RequestStatus}="Product" and
sum({@1OpenReqNew},{table.group}) > 0

This would average those date differences only if the status = Product and if there is at least one status in the group = New.

Not sure whether you want this reset on change of some group or not.

If this doesn't do what you want, I think you should explain what this summary is supposed to be doing in more depth.

-LB

 

[willz99ta]

PERFECTION.

Yes I did need to summarize without a group. Thanks for your help --- again.

[Singing] LBass, did you know your my hero! [/singing]

Will