×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

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!
  • Students Click Here

*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

Jobs

Help need to create a Formula

Help need to create a Formula

Help need to create a Formula

(OP)
I have the following data

Territory  TargetName QtrTarget   Opportunity  Qtr      Month     MonthTarget
---------    -------------   ------------   -------------  ---      -------      ---------------

Aero        AeroQ1              90000  Opp1            Qtr1   Jan         30000
Aero        AeroQ1              90000  Opp2            Qtr1   Jan         30000
Aero        AeroQ1              90000  Opp3            Qtr1   Feb        30000
Aero        AeroQ1             90000  Opp4            Qtr1   Feb        30000
Aero         AeroQ2           120000  Opp5            Qtr2   Apr       40000
Aero         AeroQ2           120000  Opp6            Qtr2   May      40000
Aero         AeroQ2           120000  Opp7            Qtr2   May      40000

I created a formula field to create MonthTarget based on the Qtr Target.
 I use cross tab report. Row summarisation is by Territory.
Column summarisation is Qtr and then Month.  Summary field is max(monthtarget)
(say for jan it will be 30000), this will  very well apply to month.
But for qtr ,say for eg qtr1 I need to get (30000 * 3) . How can I acheive this?

my output should be:

Territory Name    Jan         Feb        March     Qtr         Apr       May      Jun      Qtr
Aero                   30000    30000     30000     90000    40000    40000   40000  120000

If it is not possible using crosstab, how can I do it using standard report. I am using Crystal 8.0

Thanks,
Arthi

RE: Help need to create a Formula

In order to achieve the results you want (as indicated in your sample result set), you would create a cross-tab report with the following fields:

Row:  Territory
Column1:  Qtr
Column2:  Month
Summarized Field:  MonthTarget
Summary Operation:  Maximum (MonthTarget)

The cross-tab will subtotal the values for all three months in the Quarter which should be equal to the value in QtrTarget if your  MonthTarget Formula is correct.  Change the text Header for the Column Totals to "Qtr" and you're good to go.

RE: Help need to create a Formula

(OP)
I had already done as you had told to do.
Except this:
"Change the  text Header for the Column Totals to "Qtr" and you're good to go"

Could you please let me know which column total are you talking about?

If I use Max(MonthTarget) as summaryfield for Month, won't the same thing  be used for Qtr also?

Thanks,
Arthi



RE: Help need to create a Formula

You are correct, this won't work in a cross-tab, becuase you want some to be Max and others to be a sum.   You can't use a formula that refers to cells in a Xtab.

You can do this using conditional formulas:

Creat 12 formulas that say something like:
@MonthTarget1:

if Month = Jan
then MonthTarget
else 0

Group the report by territory and create summary fields (using Max) of these 12 columns.  Hide the details and group headers to get a rough looking Cross-tab.

Now you can create a Qtr formula that is
Max(Monthtarget1,territory) +
Max(Monthtarget2,territory)+
Max(Monthtarget3,territory)


And put these in the appropriate spot on the Group Footer.

Ken Hamady
http://www.kenhamady.com/
Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

RE: Help need to create a Formula

(OP)
Thank you very much for your help, Ken. I tried your solution and it worked.
But only thing is I have to create a lot of formula fields( in multiples of 12 ).
Is there a limit on the number of formula fields that can be created on a report?


Thank you ver much,
Arthi


RE: Help need to create a Formula

Unless I misunderstand the expected result, I disagree.  I was able to replicate the expected output (as indicated above) using a simple cross-tab.  The key to my logic is that the MonthlyTarget formula field is calculated by Jarthi as being (QuarterTarget/3).  Based on this presumption, the cross-tab should correctly subtotal the 3 MonthlyTarget values back into the QuarterTarget(MonthlyTarget*3) without any further intervention.

Jarthi's output was:

Territory Name    Jan         Feb        March     Qtr         Apr       May      Jun      Qtr
Aero                   30000    30000     30000     90000    40000    40000   40000  120000

My Output was:

Territory Name    Jan         Feb        March     Total         Apr       May      Jun      Total
Aero                   30000    30000     30000     90000    40000    40000   40000  120000

by changing the "Total" text headers to Qtr, my results equal Jarthi's.

RE: Help need to create a Formula

The limit is has more to do with memory and processing than a fixed number.   What is your rough calculation of the number needed?  The summary fields don't need to be formulas, you can simply use Insert - Summary.

Ken Hamady
http://www.kenhamady.com/
Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

RE: Help need to create a Formula

(OP)
Hi rhonik:
I tried your suggestion but didn't work for me still.
I am trying Kenhamady's method.

Thanks for your help,
Arthi

RE: Help need to create a Formula

(OP)
Hi Ken:

I created this MonthTarget1 formula field which is the maximum for a month for a territory.
Now I want to create a grand total of the @MonthTarget1(which should be the sum of the maximums) . Should I create a running total field?
Please advice.

Thanks,
Arthi


RE: Help need to create a Formula

You will need a an "old style" running total, using variables.  This is the only way to do a summary of a summary field.  See the FAQ on running totals and follow the 3-formula technique.

Ken Hamady
http://www.kenhamady.com/
Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

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