Search function in a Pivot Table
 

How would I write the function in an Inserted Calculated field to a Pivot
Report to search Field1 for "Prospects", if present, insert amount from
Field2, if not, return 0?

Search function in a Pivot Table
 

All text has the value of zero, so you can't test for specific text in a
calculated field.

Maybe you could add a field in the source data, and use a formula to
test Field1. Then, sum this new field in the pivot table.

HL Questions wrote:
How would I write the function in an Inserted Calculated field to a Pivot
Report to search Field1 for "Prospects", if present, insert amount from
Field2, if not, return 0?


--
Debra Dalgleish
Contextures
www.contextures.com/tiptech.html
Blog: http://blog.contextures.com

Search function in a Pivot Table
 

Thanks. I've added the columns needed in the source data. My pivot table
reports an employee's time in total and by client, prospecting, admin. Each
employee is summed to an office and the offices sum to a department. Columns
& Rows as follows

Dept Office Empl Client Total Time Client Prospect Admin
CS CM John AAA 40 40
CS CM John BBB 30 30
CS CM John CCC 20
20
CS CM Jane AAA 80 80
CS SF Tom CCC 50
50
CS SF Tina DDD 60 60

I would like to create a calculated field for each of client time, prospect,
admin as a percentage of the Employees total time. Then, for the office, the
sum of each of client time, prospect, admin as a percentage of all the
Office's Employees' Total Time. Then, for the Dept, the sum of each of
client time, prospect, admin as a percentage of all the Dept's Employees'
Total time.

I can am only able to write the formula for: Prospect/Total Time for the
calculated field. Which always returns 100% for each of the above
categories. What formula could be used?


"Debra Dalgleish" wrote:

All text has the value of zero, so you can't test for specific text in a
calculated field.

Maybe you could add a field in the source data, and use a formula to
test Field1. Then, sum this new field in the pivot table.

HL Questions wrote:
How would I write the function in an Inserted Calculated field to a Pivot
Report to search Field1 for "Prospects", if present, insert amount from
Field2, if not, return 0?


--
Debra Dalgleish
Contextures
www.contextures.com/tiptech.html
Blog: http://blog.contextures.com