Proper syntax for column adds and subtracts 1

[hld24]

I have column A - Required Amount of Inventory

Column C - Amount of Inventory ordered
Column D - Amount of Inventory Received

I am interested in Column E - Amount needed to be ordered.

The first three columns are alrady on the report and are correct using Running totals.
I need to take columns C and D add them together and then subtract that subtotal from column A to get column E.

CR10

 

[lbass]

If you are using running totals, then create a formula:

{#rtA}-{#rtC}-{#rtD}

Place this in the same section as the running totals.

-LB

 

[hld24]

In place of the A , C and D I pulled in the report fields that they represent. Once I did that and checked the formula I got a "Field Name not known" message.
The formula ia as follows"

{#rt{@Sample Size}}-{#rt{@Files on Order}}-{#rt{@Files Received}}

Your help is greatly appreciated.

 

[lbass]

Are you really using running totals (field explorer->running totals)? If so, then go to the field explorer->formula->new and then select the running totals you are using and use them in a formula like this:

running total for colA - running total for colC - running total for colD

-LB

 

[hld24]

Yes I am using the running totals and I have done exactly as requested. Then I get the following message THE FOLLOWING TEXT DOES NOT APPEAR TO BE PART OF THE FORMULA.

(@Sample Size) is column A and so on.

running total for {@Sample Size}- running total for{@Files on Order}- running total for{@Files Received}

 

[lbass]

You are taking me too literally. First of all, if you were really using running totals, they would appear in the formula editor like this:

{#Sample Size}

NOT with an @ sign. So what is the content of {@Sample size}?

-LB

 

[hld24]

The {@Sample size}is a formula field created to populate a distinct number based off of other field criteria.

IE:

(If {FILE1.Name}="RB" and
{FILE1.Review.Type}="NF"
then 25 else
(If {FILE1.Name}="RB" and
{FILE1.Review.Type}="F"
then 25
else 10))

 

[lbass]

So you are not using running totals. If you are just trying to add formulas together, then insert a new formula like this:

{@Sample Size}-{@Files on Order}-{@Files Received}

This is a formula for the detail (where I'm assuming you are placing the formulas it contains). If you then want to summarize this at a group or grand total level, place this formula in the detail section and right click on it->insert summary->choose the group level or grand total level.

-LB

 

[hld24]

In the formula editor I added the formula's for {@Files on Order}and{@Files Received}. They were also entered into the Running totals section and that is why I steered you in the wrong direction inadvertently. Than I created a fourth formula entitled Additional to order and used the enclosed formula {@Sample Size}-{@Files on Order}-{@Files Received} and when I check the validity of this formula it tells me that the {@Files on Order} should be a number. If I switch the order of the last two (Files on Order and Files received, I get the same results. The formula inside the two (Files on Order and Files received) work just fine by themselves.

 

[lbass]

Please show the content of each formula, and if you have any formulas inside the formulas, show the content of those, too.

-LB

 

[hld24]

This is the formula for SAMPLE SIZE
(If {FILE1_Review.Name}="RB" and
{FILE1_Review.Type}="NF"
then 25 else
(If {FILE1_Review.Name}="RB" and
{FILE1_HMDA_Review.Type}="F"
then 25
else 10))

This is the formula for FILES RECEIVED:
ToNumber({FILE1_Review.Type}= ["F","NF"] and
{FILE1_Review.File_Status} in ["In Review","Tree"])

This is the formula for Files on Order:
ToNumber({FILE1_Review.File_Status} in ["Ordered for Review"])

And this is the formula for Additional to Order:
ToNumber({@Sample Size})-
(ToNumber({@Files on Order})+ ToNumber ({@Files Received}))

I am adding the two formulas (@Files on Order) and (@Files Received) and than subtracting that from (@Samplesize). I have addressed the error when asking for a number by adding the function "ToNumber".

Some groups are coming in with the correct results yet other groups aren't. Just a blank space. I seem to fix one issue (with your help) and another crops up. Please look at my formulas and I look forward to your inout.

 

[lbass]

These formulas make no sense:

This is the formula for FILES RECEIVED:
ToNumber({FILE1_Review.Type}= ["F","NF"] and
{FILE1_Review.File_Status} in ["In Review","Tree"])

This is the formula for Files on Order:
ToNumber({FILE1_Review.File_Status} in ["Ordered for Review"])

And this is the formula for Additional to Order:
ToNumber({@Sample Size})-
(ToNumber({@Files on Order})+ ToNumber ({@Files Received}))

If you want to count files receoved and on order, then your formulas should look like:

//{@filesreceived}:
if {FILE1_Review.Type}= ["F","NF"] and
{FILE1_Review.File_Status} in ["In Review","Tree"] then 1

//{@Filesonorder}:
if {FILE1_Review.File_Status} in ["Ordered for Review"] then 1

{@Samplesize} is okay as is, but needs to have a summary inserted on it, I'm guessing.

You would place these in the detail section and right click on them and insert a summary (a SUM, NOT a count) at a group or report level.

At what level are trying to do the calculation? Is it per detail row? Per Group? Per Report?

-LB

 

[hld24]

Per group and the groups are defined as "F" and "NF

 

[lbass]

So you have a group on {FILE1_Review.Type}? Then your calculation formula (maybe) should look like the following (assuming that {@samplesize} represents a figure for the group, not each detail):

{@samplesize}-sum({@filesreceived},{FILE1_Review.Type})-
sum({@filesonorder},{FILE1_Review.Type})

...using the formulas from my last post. Then place the above formula in the group header or group footer.

-LB