Formula result and positioning.

[Secretgeek]

Afternoon everyone,

I have a formula that returns the percentage of one value against another. The formula itself is working correctly.

count({@Within IGO Deadline}) % Count ({Main.ID})

My records are grouped first by Legislation and then by Quarter.

My problem is that the formula is only returning the result from a calculation of ALL records retrieved no matter where the formula is placed on my report.

I'm a beginner with Crystal so am probably missing something obvious but I expected it to only return the result of the particular group I placed it in, for example returning the percentage for that particular Legislation if I'd placed it group footer 1.

Any help would be appreciated.

Thanks.

 

[lbass]

Two things. First, the count of a formula will return the number of times it is executed, not the count based on some condition, so we need to see the content of the formula you are summarizing {@Within IGO Deadline}.

Second, you need to add the group condition to your formula. I'm not sure like this:

count({@Within IGO Deadline},{table.groupfield}) % Count ({Main.ID},{table.groupfield})

You would need a different formula per group.

-LB

 

[Secretgeek]

Thanks. The {@Within IGO Deadline} definitely works correctly because I've checked the numbers it's returning in other ways.

From what you've said it's the '{table.groupfield}' that I'm missing.

This is new to me. Where do I pick that from? I've had a look around and can't see it.

Thanks.

 

[Secretgeek]

Actually, I've almost got it.

And realised I'm being a bit of a muppet at the same time.

{table.groupfield} is the field from my source that I've used to group my records with, yes? Ok, that works fine for the Legislation group but now I need to sort the Quarter group and this is slightly different.

Actually I've just had an idea....

 

[Secretgeek]

Sussed it. Many thanks for your help Lbass. Another fine job!

count({@Within IGO Deadline},{Main.NCC Date Received},"quarterly") % Count ({Main.ID},{Main.NCC Date Received},"quarterly")

I'm going home now.

 

[lbass]

Please understand that you should NOT be using a count on a conditional formula--it WILL be correct unless there is a record that doesn't meet the criterion, and then the result will be off by one, since the default value of zero will count as one. There is a method for correcting for this, but you have not demonstrated (by showing the content of the formula) that you have implemented it.

-LB

 

[Secretgeek]

Ah, ok. So 'Sum' is better as it won't pick up those records that fail the test?

In that case I need to take a closer look at my data because it doesn't look like my numbers are adding up to what I was expecting.

:-(

It's going to be a long day.

 

[Secretgeek]

After wracking my brain somewhat I've come to the conclusion that one half of what I'm calculating is wrong.

What I actually need in this half is something along the lines of:

Sum {IGO Deadline} Where {Closed Date} is not null and {IGO Deadline} <= {Closed Date}

This will give me the figure to use to calculate my percentage of cases that have been closed within their deadline.

I'm off to try and work out how to put this into Crystal lanuguage. Any pointers would be appreciated.

 

[lbass]

Create a formula {@withindeadline}:

if isnull({Closed Date}) or
{IGO Deadline} > {Closed Date} then
0 else 1

Then use:

sum({@withindeadline},{Main.NCC Date Received},"quarterly") % Count ({Main.ID},{Main.NCC Date Received},"quarterly")

This assumes that you don't have row inflation. If you do, you would have to take a different approach.

-LB

 

[Secretgeek]

Thanks Lbass.

In the interim between my last message and yours I somehow managed to create something almost identical to you and got it working!

Many thanks for your help.