Please help with AND/OR clause

[ericb123]

Using CR11 and sql, I have a table with a 1-many relationship, and I'm trying to do a calculation on 2 fields. My data looks like this:

Claim Num Error ID Resolution ID Formula
6093960 22 ?
6093960 24 5 2.00
6093960 25 ?
6093960 41 ?

etc.

My formula is trying to account for both records with an Error ID of 22 or 24, and where the resolution ID is either 3, 5, or NULL.

So my formula is:

if {table.ErrorId} in [22,24] and
({table.ResolutionId} in [3,5] OR
IsNull({table.ResolutionId})
then
total := total + (1 * 0.02) *100

the last part is to add to the running total and make it a percent. But both the 1st and 2nd records should have a formula result of 2, but only the 2nd record with the resolutionID of 5 is having the formula applied.

I'm hoping this is something silly in my formula I'm missing? Any help is greatly appreciated, thanks!

 

[AndyMc]

Try putting your check for nulls first in your formula:

if IsNull({table.ResolutionId}) or
({table.ErrorId} in [22,24] and
{table.ResolutionId} in [3,5])

then
total := total + (1 * 0.02) *100


Andy

 

[lbass]

I think that should be:

if (
IsNull({table.ResolutionId}) or
{table.ResolutionId} in [3,5]
) and
{table.ErrorId} in [22,24] then
total := total + 2;

-LB

 

[ericb123]

Awesome, thanks! That works great. One more criteria however:

In my sample data, I was just informed by my manager, that if an errorID appears more than once, they only want to count it 1 time.

So there are 2 rows with errorID that appear more than once: One row with errorID of 22, and one with errorID of 24.

This should only count as 2% (for both). My formula has it at 4%, (2x2).

Is there a way to tell my formula to only count any errorID's found in my [] to count them only once?

Thanks again!

 

[lbass]

Please show some sample data and show the value that you WANT to appear in your count in a separate column, as it accumulates.

-LB

 

[ericb123]

Thanks lbass!

The data is a table of errors per claim, per person that are reviewed for accuracy. So the report is grouped by person, then by claim number. The error table can have multiple claim#'s in it, because there can be more than 1 error on a reviewed claim.

However, some error types should only be counted once, other error types should be counted for each time they appear. So I have 2 formulas: count_mult and count_once

The data SHOULD look like this:

Claim# ErrorID ResolutionID {@count_once} {@count_mult}
12345 3 5 2.00 0.00
12345 4 2.00 0.00
12345 29 3 2.00 0.00
12345 10 2.00 3.00
12345 11 5 2.00 6.00
12345 12 2.00 9.00
_________________________________________________________
Totals: 2.00 9.00

The formulas should count errorID's 3,4,29 only once, but 10,11,12 as each time they appear. The totals just add them up for each claim#

The formula I'm using for @count_once is:
......
if (
IsNull({AuditTicketEntry.AuditTicketEntryResolutionId}) or
{AuditTicketEntry.AuditTicketEntryResolutionId} in [3,5]
) and
{AuditTicketEntry.AuditErrorId} in [3,4,29] then
sing := sing + (1 * 0.12) *100

else.....

but it's still adding them each time it appears.

The error for @count_mult is:

if (
IsNull({AuditTicketEntry.AuditTicketEntryResolutionId}) or
{AuditTicketEntry.AuditTicketEntryResolutionId} in [3,5]
) and
{AuditTicketEntry.AuditErrorId} in [10,11,12] then
mult := mult + {#CLAIM_error rate multi} * 0.03 * 100

else....

And the running total {#CLAIM_error rate multi} in this formula is just a running total of the number of times the record apprears so that it can count it multiple times (this is probably not the best way of doing this, but it seems to work)


(
IsNull({AuditTicketEntry.AuditTicketEntryResolutionId}) or
{AuditTicketEntry.AuditTicketEntryResolutionId} in [3,5]
) and
{AuditTicketEntry.AuditErrorId} in [6,8,10,11,12,14,16,17,19,20,24,22,28]



Thanks again for all your help!

 

[lbass]

I don't really know why you using calculations like 1*.03*100 which is the same as 3. It seems overcomplicating.

I think you should start over with formulas like this:

if (
IsNull({AuditTicketEntry.AuditTicketEntryResolutionId}) or
{AuditTicketEntry.AuditTicketEntryResolutionId} in [3,5]
) and
{AuditTicketEntry.AuditErrorId} in [3,4,29] then 2

Just insert a maximum on this at the claim group level.

I'm assuming you are showing a type when you say .12, and that you really mean .02.

For the other formula, use:

if (
IsNull({AuditTicketEntry.AuditTicketEntryResolutionId}) or
{AuditTicketEntry.AuditTicketEntryResolutionId} in [3,5]
) and
{AuditTicketEntry.AuditErrorId} in [10,11,12] then 3

Insert a sum on this at the group level.

-LB

 

[ericb123]

Thanks again, but how do I "insert a maximum" on something?

Thanks for your help!

 

[lbass]

Place the formula in the detail section->right click->insert summary: maximum->choose the group level or the grand total level.

-LB

 

[ericb123]

Got it, thanks. Now that I have the Maximum for each group, how can I add all these Maximum's together for a total?

Say I have:

John Doe (GH1)
claim#12345 (GH2)
{@formula} = 2 (GF2)
claim#54321 (GH2)
{@formula} = 3 (GF2)
TOTAL FOR John Doe (GF1) = 5

Every time I try to add another summary of the detail level, whether I do a count or a sum, it comes out too high.

 

[lbass]

Use a variable. Create these formulas:

//@reset} to be placed in GH1:
whileprintingrecords;
numbervar sumx;

if not inrepeatedgroupheader then
sumx := 0;

//{@accum} to be placed in the GF2:
whileprintingrecords;
numbervar sumx := sumx + {@formula};

//{@display} to be placed in GF#1:
whileprintingrecords;
numbervar sumx;

-LB