Hi,
I did originally post this question on the thread "Averages in Pivot Tables", but as that thread was last used in 2006, it seems my query may have been missed, so I am starting a new thread...just wanted to pre-empt a telling off from the moderators!
I am working with a database which uses a SQL query to return rows of data, which are then summarised into a pivot table. The data relates to attorney's timesheets, and the data is sorted in the pivot table into four layers:
1. Attorney Group
2. Attorney Name
3. Date
4. Client
The subsequent columns show how many hours were spent on different types of activities for those clients e.g. chargeable time, non-chargeable time, travelling time etc.
As I have mentioned, the source of the data is a SQL database, and even looking for just one day's data can result in 1,000+ rows. There are formulas used to manipulate the source data before the data is used to create the pivot tables and charts.
Most of the time, I need to sum the data per attorney, and then if necessary, drill down for a more detailed view by date/client, so the pivot table is perfect.
However, I also need to look at averages based on attorney groups. For example, how much client work does a partner do on average, or how much partner time is spent on management each day on average.
I therefore need to work out an average based on the calculated sum for each attorney - as the previous user mentioned, using the Average function in the table just gives me an average of ALL the data entries, which is meaning less as there could be five different entries for management time in one day, at various times in the day - all I am interested in is the total for the day.
Unfortunately, I am not advanced enough with SQL scripts to be able to manipulate the data at source, so that's a no-no.
Any help would be much appreciated, and if you want, I can send a file with sample data attached...not sure how to get it on to here.
Thanks.
I did originally post this question on the thread "Averages in Pivot Tables", but as that thread was last used in 2006, it seems my query may have been missed, so I am starting a new thread...just wanted to pre-empt a telling off from the moderators!
I am working with a database which uses a SQL query to return rows of data, which are then summarised into a pivot table. The data relates to attorney's timesheets, and the data is sorted in the pivot table into four layers:
1. Attorney Group
2. Attorney Name
3. Date
4. Client
The subsequent columns show how many hours were spent on different types of activities for those clients e.g. chargeable time, non-chargeable time, travelling time etc.
As I have mentioned, the source of the data is a SQL database, and even looking for just one day's data can result in 1,000+ rows. There are formulas used to manipulate the source data before the data is used to create the pivot tables and charts.
Most of the time, I need to sum the data per attorney, and then if necessary, drill down for a more detailed view by date/client, so the pivot table is perfect.
However, I also need to look at averages based on attorney groups. For example, how much client work does a partner do on average, or how much partner time is spent on management each day on average.
I therefore need to work out an average based on the calculated sum for each attorney - as the previous user mentioned, using the Average function in the table just gives me an average of ALL the data entries, which is meaning less as there could be five different entries for management time in one day, at various times in the day - all I am interested in is the total for the day.
Unfortunately, I am not advanced enough with SQL scripts to be able to manipulate the data at source, so that's a no-no.
Any help would be much appreciated, and if you want, I can send a file with sample data attached...not sure how to get it on to here.
Thanks.
