Crosstab:: Inserting A Formula Based On Two Summarized Fields

[zcalebz]

Hi I have this type of crosstab working now:
10AM 11AM 12PM
Sales (total) 107 215 175
Transactions (count of) 10 16 14
Clerks (distinct count of) 2 3 1

What I want is One more Row:
Transactions Per Clerk 5 5.33 14

I have built the formula, but can't insert it into the crosstab(count of transactions/dist count of clerks)
I assume because it is an aggregate of an aggregate.....
Is there any way to do a simple division between summarized fields???
Any help is greatly appreciated.....

Thanks in advanced,
Caleb

 

[lbass]

You need to do a manual crosstab to do this. You would have to create formulas something like the following:

{@Sales10}:
if time({yourdatetimefield}) = time(10,00,00) then {table.salesamt}

{@Trans10}:
if time({yourdatetimefield}) = time(10,00,00) then {table.trans}

{@Clerk10}:
if time({yourdatetimefield}) = time(10,00,00) then {table.clerk}

If your column headings reflect time ranges, e.g., 10AM to 11AM, the formulas would have to be adjusted to say "in time(10,00,00) to time(11,00,00)."

Repeat for each hour in the crosstab. If you have a group, (let's say you grouped on {table.store}), then you would insert a summary on the formulas you want summed. For the counts and distinct counts, you would have to create running totals. Using the running total editor, create {#trans10} by selecting count of {@trans10}, evaluate using a formula:

{@trans10} <> 0 //or &quot;&quot; if {@trans} is a string

Reset on change of {table.store}. Repeat for {@clerk10} to create {#clerk10}, using distinct count. Now you can write the summary formula you wanted:

{#trans10}/{#clerk10}

Repeat for each hour. Place the formulas, along with your summaries and text which identifies the summaries, in the group footer and arrange however you like. Then you can suppress the details section.

-LB

 

[dannab]

For {@svcfactor}
Im getting error:
the summary / running total field could not be created

dannab