Help need to create a Formula

[jarthi]

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

 

[rhinok]

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.

 

[jarthi]

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

 

[kenhamady]

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)

 

[jarthi]

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

 

[rhinok]

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.

 

[kenhamady]

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)

 

[jarthi]

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

Thanks for your help,
Arthi

 

[jarthi]

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

 

[kenhamady]

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)