×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Basic financial formula badly needed

Basic financial formula badly needed

Basic financial formula badly needed

(OP)
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 :)

RE: Basic financial formula badly needed

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") 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

RE: Basic financial formula badly needed

(OP)
Sorry, they're both 4 position numeric fields.

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

RE: Basic financial formula badly needed

(OP)
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")

RE: Basic financial formula badly needed

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

RE: Basic financial formula badly needed

(OP)
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})


RE: Basic financial formula badly needed

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

RE: Basic financial formula badly needed

(OP)
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.

RE: Basic financial formula badly needed

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close