Pivot calculations, is it possible this way?

[Guest]

Hi,

I've a datadump from a database containing work hours from five different
departments, categorized as several different types like 'sick leave', 'on
training' etc and of course the usual workstuff categories...

I'ld want a pivot table of a specifik work type per department, like 'on
training', but I'ld like the value to be proportional to the amount of
personal on that specific department. Is that possible, how?


Any help would be appreciated!

 

[tristan.huxley]

Hi Sebastian,

I can only assume that you already have the pivot table there...

on the top left of the table, you should have something like "Count
of..." or "Sum of ...", if you double click on this, you should have a
dialogue display with the name of "PivotTable Field"... Click on
"Options", and where it has "Show data as...", you can select from the
drop down list, and then use the "Base Field" and "Base Item" to have
it calculate on the item/s you need...
Your other option is to (on the PivotTable toolbar - to show this goto
View > Toolbars > PivotTable), then you can select PivotTable >
Formula's > Calculated Field

I'm pretty sure that this is what you are after, or will at least put
you on the right track

 

[Guest]

Thank you for the reply,

I'm still a bit confused... I know about the techniques you described but I
can't get the logic to work, or rather I can't get the pivot to do what I
want it to do...

I'll try to describe my problem a bit further, an example of my table data:

name;department;month;type;hours
john;sales;12/2006;on training;22
john;sales;12/2006;usual work;99
john;sales;12/2006;meeting;10
john;sales;01/2007;sick leave;160
sara;IT;01/2007;usual work;160
..
..
..

you get the point.

Now my problem is that there is no problem to get a count of how many people
from each department in a pivot table and a summary of a type, for instance
'sick leave' per department. But how do I compare those two row values in a
third row in the pivot (per department), I want 'summary of type'/'count of
personal in department' in the third row...
There is probably something easy that I'm missing, but I just cant get it to
work.

My problem is that I cant do the count in the table as a workssheetfunction
because there is several entries per person per month, like my example table
where john has 3 entries in 12/2006...

so the main q is, how can I use different values already in the pivot in a
formula and show the result in my own custom row, is it possible, hor how is
this supposed to be done?

/Sebastian N

 

[Guest]

forgot to mention, that if someone can tell me how to get the count of
persons per department to work in the pivot calculation ,then I can get it to
work, but it dont understand count(name), always gives the value 1...

 

[Debra Dalgleish]

YOu can use the technique shown here to get a unique count in the source
table, then add use that field in a Calculated Field in the pivot table:

http://www.contextures.com/xlPivot07.html#Unique

 

[Guest]

Tank you Debra,

A very useful worksheetfunktion, that one solved other problems...
Now I'm able to work around the problem. Though I'ld still want to know if
it's possible to use results from another pivot datavalue field in the pivot
formula calculation field?
If I insert the name field in the data area as a calculate field, can I use
the result in the pivot formula calculate field?

Or is it just ment for basic calculation like + - * / for datavalues already
in the table?


/Sebastian N

 

[Debra Dalgleish]

A calculated field uses the sum of the fields that it references. If you
use the name field, which is text, the sum will always be zero. So you
could use it, but the result would not be what you want.

 

[Guest]

Last follow up question... so that I'm certain that I've understood this...

so you mean that I can't use the result of a named field if I've used a
count function on that field, result is numeric although the field itself is
a text field?

I just want to know if the result of a function field (count, average etc..)
can be used in another formula calculate field or if I can use a function
directly in the formula calculate field itself like = hours/count(name)

/Sebastian N

 

[Debra Dalgleish]

It doesn't matter what summary function you've used on a field in the
pivot table, e.g. Count of Name. The calculated field uses the sum of
the underlying data. Name is a text field, so its sum will be zero.

If you use Count(Name) in the calculated field, it will just count the
sum of the underlying data, and will always return a 1.