How to write this formula formula

[shams100]

I have this situation and I will appreciate any help:

I have table with 4 fields.

DispValue, Max , Min and month

I want to write a formula to count the fields for every month with that condition:

If Max is null use the DispValue field with condition that it is not < that the Min field
If Min is Null use the display field with condition that it is not > than the Max field


Dim x,v,w,z
x=0
z=0
for x=0 to count({all the record})-1

if isnull({Max}) then
v={DispValue}
else
v={Max}
end if
if isnull({Min}) then
w={DispValue}
else
w={Min}
end if
if {DispValue} < w or {DispValue} > v then

z=z+1

else
z=z
end if

next
formula=z

 

[lbass]

Please show some sample data at the detail level with the results you expect to see.

-LB

 

[shams100]

Thanks for your respond:

Sample of the data"

Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
4 5 7 8 5 4 3 0 5 5 6 8

The report has year as a parameter.

Thanks

 

[dgillz]

That is the desired result, is it not? Lbass asked for sample data, which is likely several records.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[lbass]

dgillz is right. I meant for you to show multiple examples of records showing the four fields and then show how you would expect them to be counted.

-LB

 

[shams100]

Month DispV Min Max
1 299 500 NULL
2 291 500 NULL
3 360 500 NULL
4 760 500 NULL
5 813 500 NULL
6 755 500 NULL
7 683 500 NULL
8 664 500 NULL
9 600 500 NULL
10 462 500 NULL
11 471 500 NULL
12 762 500 NULL
1 5.97 0 5
2 13.13 0 5
3 12.83 0 5
4 8.76 0 5
5 10.46 0 5
6 9.58 0 5
7 9.18 0 5
8 8.41 0 5
9 9.09 0 5
10 8.51 0 5
11 11.8 0 5
12 14.48 0 5
1 7.83 0 2
2 5.37 0 2
3 7.19 0 2
4 7.44 0 2
5 8.93 0 2
6 8.74 0 2
7 7.39 0 2
8 7.64 0 2
9 6.12 0 2
10 11.25 0 2
11 11.88 0 2
12 9.27 0 2
1 0.04 NULL 2
2 0.06 NULL 2
3 0.11 NULL 2
4 0.11 NULL 2
5 0.05 NULL 2
6 0.05 NULL 2
7 0.05 NULL 2
8 0.11 NULL 2
9 0.03 NULL 2
10 0.04 NULL 2
11 0.1 NULL 2
12 0.08 NULL 2
1 38.18 40 NULL
2 39.21 40 NULL
3 39.81 40 NULL
4 40.47 40 NULL
5 40.84 40 NULL
6 40.8 40 NULL
7 40.96 40 NULL
8 41.09 40 NULL
9 41.03 40 NULL
10 40.6 40 NULL
11 40.5 40 NULL
12 40.14 40 NULL
1 58.6 65 NULL
2 60.22 65 NULL
3 62.49 65 NULL
4 64.6 65 NULL
5 65.52 65 NULL
6 65.6 65 NULL
7 66.26 65 NULL
8 66.65 65 NULL
9 66.58 65 NULL
10 66.29 65 NULL
11 66.34 65 NULL
12 66.32 65 NULL
1 13.64 NULL 15
2 12.71 NULL 15
3 13.7 NULL 15
4 11.11 NULL 15
5 10.57 NULL 15
6 11.63 NULL 15
7 9.51 NULL 15
8 12.22 NULL 15
9 13.22 NULL 15
10 8.43 NULL 15
11 8.54 NULL 15
12 11.4 NULL 15
1 16.83 NULL 20
2 12.44 NULL 20
3 17.98 NULL 20
4 15.78 NULL 20
5 14.21 NULL 20
6 16.1 NULL 20
7 19.23 NULL 20
8 17.58 NULL 20
9 15.7 NULL 20
10 22.32 NULL 20
11 17.01 NULL 20
12 21.48 NULL 20
1 14.26 NULL 9
2 13.63 NULL 9
3 13.5 NULL 9
4 14.28 NULL 9
5 12.4 NULL 9
6 12.81 NULL 9
7 11.09 NULL 9
8 11.61 NULL 9
9 14.46 NULL 9
10 13.61 NULL 9
11 11.59 NULL 9
12 9.46 NULL 9
1 10.94 -2 3
2 10.3 -2 3
3 10.19 -2 3
4 10.72 -2 3
5 10.72 -2 3
6 13.17 -2 3
7 11.79 -2 3
8 12.69 -2 3
9 12.45 -2 3
10 12.32 -2 3
11 13.04 -2 3
12 14.48 -2 3
1 -7.63 -4 1
2 -9.7 -4 1
3 -10 -4 1
4 -9.8 -4 1
5 -10.5 -4 1
6 -8.47 -4 1
7 -8.11 -4 1
8 -9.49 -4 1
9 -10.7 -4 1
10 -9.26 -4 1
11 -10.6 -4 1
12 -9.74 -4 1
1 4.13 -3 1
2 4.58 -3 1
3 5.38 -3 1
4 6.72 -3 1
5 6.4 -3 1
6 6.81 -3 1
7 5.6 -3 1
8 7.21 -3 1
9 6.73 -3 1
10 6.91 -3 1
11 8.3 -3 1
12 7.11 -3 1
1 2 5 NULL
2 2 5 NULL
3 2 5 NULL
4 7 5 NULL
5 6 5 NULL
6 5 5 NULL
7 6 5 NULL
8 6 5 NULL
9 5 5 NULL
10 4 5 NULL
11 4 5 NULL
12 5 5 NULL
1 1 0 0
3 1 0 0
5 2 0 0
9 1 0 0
10 1 0 0
11 14 0 0
12 3 0 0

Part of the reprot will be:


Jan Feb Mar Apr May Jun
11 10 11 7 7 6




Formula should be:
Use the display value for the max if there is no max valueUse the display value for the min if there is no min value
If ((DisplayValue < MinValue) or (DisplayValue > MaxValue)) then (IssueCount = IssueCount + 1)

 

[dgillz]

So how do you come up with January being 11 in the sample you posted?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[shams100]

This is another Example to make it very clear. This example for one month only.

if Max is null use the DispValue field with condition that DispValuenot is not < then the Min field

If Min is Null use the display field with condition that it is not > than the Max field




DsipVAlue MIN MAX
471 500 NULL 1
11.8 0 5 1
11.88 0 2
66.33 64 null
First line =1 becasue
MAX is nul . MAX =displayvalue Formula= 471< 500 or 471>471

Second line issue =1 because
11.8<0 or 11.8>5


Third line =1 becaue
11.88<0 or 11.88>2

Forth line =0 because
because MAX is nul . MAX =displayvalue Formula=66.3< 64 or 66.3>6.33

 

[lbass]

Please reread your post, as the logic makes no sense to me and the sample doesn't match your description. For example, since 471 IS < 500, then the first row should receive a 1. In the second row, min is NOT null, as it equals 0. The third row should = 1, but none is displayed. Etc.

-LB

 

[shams100]

Sorry, this is more clear:

Use the display value for the max if max value is NULL

Use the display value for the min if min value is NULL

If ((DisplayValue < MinValue) or (DisplayValue > MaxValue)) then
Issue=1
Else
Issue=0



Eg.

DispValue Min Max Issue
_____________________________________
471 500 NULL 1
11.8 0 5 1
11.88 0 2 1
66.33 64 NULL 0

 

[lbass]

Using Crystal syntax (not Basic), try a formula like:

numbervar max;
numbervar min;
if isnull({table.max}) then
max := {table.dispvalue} else
max := max;
if isnull({table.min}) then
min := {table.dispvalue} else
min := min;
if {table.dispvalue} < min or
{table.dispvalue} > max then 1

Place this formula in the detail section and insert a sum on it to get a group level or report level summary.

-LB