CrossTab Multiple Fields Formula

[icerubes]

Evaluation (fully functional) copy - SAP Crystal Reports

Standard report structure is:-

Group #1 - Estimator
Group #2 - Debtor

Fields - Quote#, Quote Date, Job#, Job Inv $

There will not always be a Job for each Quote. Therefore for a Job to be counted or summed the JOB# must not be null.


The Crosstab format is

(columns) Date (split by Quarter)

(rows) Estimator

(summarized fields) Count Quotes, Count Jobs, Sum Job Inv$ (all Quotes), Sum Job Inv$ (this for won Jobs)

Following formulas used and then summarized for CrossTab

(@CountJob)
If {month_year.JOB#} <> "" then
1
Else
0

(Count option in CrossTab summary)

(@SumJobInv$)
If {month_year.JOB#} <> "" then
{month_year.TOT_VALUE_JOB}
Else
0

(sum option in crosstab summary)

I would like to now add
- % of jobs won to quotes completed ie. count Jobs/count quotes
- avg $ per won job. i.e.count of jobs / job inv $ (won jobs not total quote value)

I am having trouble creating the formula and then applying it to the CrossTab. i.e What is the correct formula and what is the summarized field option in Crosstab. i.e. summed or ?

thank you for your help

 

[NickieP]

I do something similar, but create a formula field for whether the quote has been won or not first to get the variants; Quote - no job, Quote & job,(we also have Job - no quote)
and then pivot using those.
You can see what % of quotes became jobs without having to have any complicated formulae fields in the pivot totals area.