Help Determining Minimum and Maximum Values…With Exceptions

[omcompany]

I have a set of holes and within each hole there are sections. (Picture a cardboard tube divided into sections and each section has a number of units in it.)

Each section has a quantity associated with it.

I need to determine the minimum and maximum quantity for these sections.

The exceptions are;

1. that I only look at the holes with more than one section
2. that the last section in the hole be ignored

A sample data set would look like this;

Hole, Section, Quantity
1, 1, 2
1, 2, 7
2, 1, 2
2, 2, 7
3, 1, 15
4, 1, 1
4, 2, 3
4, 3, 10
5, 1, 2

Grouped by Hole

Holes with only one section should be ignored. (In this example, Hole 3 and Hole 5 would be ignored)

Holes with more than one section should ignore the last section in that hole. (In this example, Hole 1, Section 2; Hole 2, Section 2; Hole 4 Section 3 would be ignored)

The Quantities to be looked at should be;

Hole, Section, Quantity
1, 1, 2
2, 1, 2
4, 1, 1
4, 2, 3

The final minimum to maximum quantity results should be;

1 to 3

Thank you in advance for your time.

 

[IanWaterman]

Group by Hole
In report or group header above hole grp

@reset
whileprintingrecords;

global numbervar minh:= 0;
global numbervar maxh:= 0;

in details
@min
If section <> min(section, hole) and section < min then min:= quantity;

@max
If section <> max(section, hole) and section > max then max:= quantity;

In report or next group footer
@display min
whileprintingrecords;

global numbervar minh:= 0;

@display max
whileprintingrecords;

global numbervar maxh:= 0;

Ian

 

[omcompany]

Ian,

Thank you very much for your help.

I am getting an error in the min and max formulas. The error comes at the first min (or max) and the error is 'A number, currency...is expected here'.

I have it grouped by hole.

The exact formula is below.

//If section <> min(section, hole) and section < min then min:= quantity;
If {ShotDeckInventory.DeckOrder} <> min({ShotDeckInventory.DeckOrder},{ShotDeckInventory.HoleID}) and
{ShotDeckInventory.DeckOrder} < min then min:= {ShotDeckInventory.Quantity};

I have all other formulas in place.

Thanks again for your time.

 

[IanWaterman]

Sorry my bad, problem with switching from SQL to Crystal syntax

functions are full spelling minimum()

minimum({ShotDeckInventory.DeckOrder},{ShotDeckInventory.HoleID})

Ian

 

[omcompany]

Ian,

I understand and thank you for your quick responses. Once I change min to minimum the error, as expected moves to the next min. I update that min to minimum and so on. Once all min are updated to minimum I get an error saying that, 'The remaining text does not appear to be part of the formula' with ':= {ShotDeckInventory.Quantity};' being highlighted.

 

[IanWaterman]

Again not being explicit enough, long day !!

Variables must be declared prior to use start @min formula with

whileprintingrecords;
global numbervar min;


and same for max but declaring max

Ian

 

[omcompany]

Ian,

The formulas now are correct in that there are no errors. Thank you.

However I am not getting the correct results.

The end results as well as looking at the data in the details section are all zeros(0). The only exception is that in the details I am seeing the quantity of 2 for Hole 1, Section 1.

Sorry for the hassle, your time is appreciated.

Report Header - @reset
Details - @min @max
Report Footer - @display min @display max

 

[omcompany]

If I make changes to the following formulas I get correct results for the @max formula.

@reset
whileprintingrecords;

global numbervar min:= 0;
global numbervar max:= 0;

@max
whileprintingrecords;
global numbervar max;

If {ShotDeckInventory.DeckOrder} <> maximum({ShotDeckInventory.DeckOrder},{ShotDeckInventory.HoleID}) and
{ShotDeckInventory.Quantity} > max then max:= {ShotDeckInventory.Quantity}

@Display max
whileprintingrecords;

global numbervar max;

max;



When making those changes to the @min formula I still get zero(0) as the results for that formula. The details section shows zero results coming in from the formula.

@min
whileprintingrecords;
global numbervar min;


If {ShotDeckInventory.DeckOrder} <> maximum({ShotDeckInventory.DeckOrder}, {ShotDeckInventory.HoleID})
and
{ShotDeckInventory.Quantity} < min then min:= {ShotDeckInventory.Quantity};

 

[omcompany]

Ian,

Here is the formula for @min that worked for me,

whileprintingrecords;
global numbervar min;


If {ShotDeckInventory.DeckOrder} <> maximum({ShotDeckInventory.DeckOrder}, {ShotDeckInventory.HoleID})
then min:= minimum({ShotDeckInventory.Quantity});

I am still testing and I am sure this is not the cleanest way to approach this but so far so good.

I also removed the @reset formula. It did not seem to make any difference in the outcome.

I will post back if I am able to break it, if not, thank you very much for your time. I have learned a lot from your help.