Basic financial formula badly needed

[faush]

First report I'm doing in Crystal reports - I've had minimal training...

Field name is CAACT1, which holds the account number.

I need all acct numbers that begin with 1000, while another field is = 00

How do I write this formula field?

Please help!
thank you

 

[dcm]

faush: You don't explain what type of field CAACT1 or the other field is but I have assumed they are both strings. Your formula is:

If (left(CAACT1,4)="1000" and left(field,2)="00&quot then true

You can then select all records where this formula is true David C. Monks
david.monks@chase-international.com

http://www.chase-international.com

Accredited Seagate Enterprise Partner

 

[faush]

Sorry, they're both 4 position numeric fields.

Thanks for your quick response, I'm trying to learn as I go here.

 

[dcm]

faush: Not sure exactly what you mean by 4 position numeric, I assume you mean has 4 integers so formula would now be:

If (CAACT1=1000 and field=0) then true
David C. Monks
david.monks@chase-international.com

http://www.chase-international.com

Accredited Seagate Enterprise Partner

 

[faush]

Thanks, but I'm still having trouble. I'll try to explain
it a little better:

CAFNCT (2 pos)
CACMPY (4 pos)
CAACT1 (4 pos)
are all numeric fields.

I need to select:

CAFNCT if = "00",
CACMPT if between numeric range 102 to 199

and then I need to add all values in CAACT1 if they are equal to "1000".

Below is my formula that does not work. Can you tell me what I am missing? Thanks so much, I appreciate any advice.

if ({CHART.CAFNCT} = "00" and {CHART.CACMPY} [102 to 199])
then Sum({CHART.CAACT1},"1000&quot

 

[dcm]

faush: First start by creating a Running Total (caact1_rt) which sums CAACT1 but evaluates according to the formula CAACT1 = 1000.

Then your original formula will read:

If CAFNCT = 0 and CACMPY in 102 to 199 then caact1_rt else 0

Note thst this will return the Grand Sum of CAACT1. If you need subtotals then you will need to choose a reset for the Running Total David C. Monks
david.monks@chase-international.com

http://www.chase-international.com

Accredited Seagate Enterprise Partner

 

[faush]

Thanks for responding. Still working on this formula, and now I need it to do something slightly differently

The value I need to show up on my report is the sum of fields CACA01 to CACA13 - these fields each represents a month and contains the balance for that particular month.

However, I need to print that total only if the conditions above it are true.

The formula does not have any errors, but I'm not getting any numeric value after I run this. If I browse the field data, the correct data does show up. How do I get this to print? I thought this was a simple formula, but I guess I keep missing something. Thanks so much for your time on this never-ending problem.

The following fields are all numeric values and represents account number, department, etc.

if (CHART.CAACT1} = "1000" and {CHART.CAFNCT} = "00"
and {CHART.CACMPY} = "1" and {CHART.CAFND1} = "01"

then
({CHART.CACA01} + {CHART.CACA02} + {CHART.CACA03} + {CHART.CACA04} + {CHART.CACA05} + {CHART.CACA06} + {CHART.CACA07} + {CHART.CACA08} + {CHART.CACA09} + {CHART.CACA10} + {CHART.CACA11} + {CHART.CACA12} +
{CHART.CACA13})

 

[dcm]

faush: The problem is that your formula is evaluated for each record. If it so happens that your conditions are not met then the default reponse will be zero rather than what I expect you are looking for which is for the continuation of the previous sum.

Try defining your formula as follows:

numbervar annual := 0;

annual := if (CHART.CAACT1} = "1000" and {CHART.CAFNCT} = "00"
and {CHART.CACMPY} = "1" and {CHART.CAFND1} = "01"

then
({CHART.CACA01} + {CHART.CACA02} + {CHART.CACA03} + {CHART.CACA04} + {CHART.CACA05} + {CHART.CACA06} + {CHART.CACA07} + {CHART.CACA08} + {CHART.CACA09} + {CHART.CACA10} + {CHART.CACA11} + {CHART.CACA12} +
{CHART.CACA13})

else

annual

David C. Monks
david.monks@chase-international.com

http://www.chase-international.com

Accredited Crystal Decisions Enterprise Partner

 

[faush]

Thanks for your response.

But I'm still getting a zero value. I did a query on this, and there is an amount in CACA03 that should be displayed after the formula runs. Any further suggestions would be extremely helpful.

 

[dcm]

faush: The zero response means that you IF test is evaluating false. Check this by doing 1 of 2 things.

1. Create another formula which just contains the IF test i.e.(CHART.CAACT1} = "1000" and {CHART.CAFNCT} = "00"
and {CHART.CACMPY} = "1" and {CHART.CAFND1} = "01"
Place this on your report and check that you are getting True responses when you expect them

2. Trim your strings to remove any spurious characters as in :
numbervar annual := 0;

annual := if trim((CHART.CAACT1}) = "1000" and trim({CHART.CAFNCT}) = "00"
and trim({CHART.CACMPY}) = "1" and trim({CHART.CAFND1}) = "01"

then
({CHART.CACA01} + {CHART.CACA02} + {CHART.CACA03} + {CHART.CACA04} + {CHART.CACA05} + {CHART.CACA06} + {CHART.CACA07} + {CHART.CACA08} + {CHART.CACA09} + {CHART.CACA10} + {CHART.CACA11} + {CHART.CACA12} +
{CHART.CACA13})

else

annual

You may find it easier if (CHART.CAACT1},{CHART.CAFNCT},{CHART.CACMPY},{CHART.CAFND1} will only contain numeric charcters to convert the whole thing to use numerics as in:
numbervar annual := 0;

annual := if val((CHART.CAACT1}) = 1000 and val({CHART.CAFNCT}) = 0
and val({CHART.CACMPY}) = 1 and val({CHART.CAFND1}) = 1

then
({CHART.CACA01} + {CHART.CACA02} + {CHART.CACA03} + {CHART.CACA04} + {CHART.CACA05} + {CHART.CACA06} + {CHART.CACA07} + {CHART.CACA08} + {CHART.CACA09} + {CHART.CACA10} + {CHART.CACA11} + {CHART.CACA12} +
{CHART.CACA13})

else

annual
David C. Monks
david.monks@chase-international.com

http://www.chase-international.com

Accredited Crystal Decisions Enterprise Partner