"The field you are moving cannot be placed" error: pivot table was working

[pete234]

"The field you are moving cannot be placed to that pivot table area."
The usual helpful message.

This error happens when dropping a time stamp field into the Totals area.
This was working perfectly fine for weeks: it gave me min/max on call start times, counts and averages. The time field on the PowerPivot sheet does come in as
12/30/1899 11:39:38 AM
And I changed the format just to Time.
From the Pivot and I can select the times and I can dump it in Rows fields which I don't need.
But I assume Powerpivot still doesn't like this somehow.
How can I fix this?
I did not have any calculated fields for this so not sure what happened.

 

[powerpivotpro]

I suspect that PowerPivot just doesn't know what aggregation you'd like to use for a Time data type. Sum? Average? Count?

You've confused your poor addin

Can you write an "explicit" measure, using the New Measure button? Then you can write an AVERAGE() or MAX() or whatever you desire.

But even then, I am very curious as to what those functions return with a time data type. You may ultimately have to switch over to some sort of integers in the PowerPivot window to get what you need.

Please keep me posted, I am quite interested in how this works out. I've never done anything with "time of day" as a measure but I am sure it's just a matter of time before I encounter it too.

 

[pete234]

I suspect that PowerPivot just doesn't know what aggregation you'd like to use for a Time data type. Sum? Average? Count?

You've confused your poor addin

Can you write an "explicit" measure, using the New Measure button? Then you can write an AVERAGE() or MAX() or whatever you desire.

But even then, I am very curious as to what those functions return with a time data type. You may ultimately have to switch over to some sort of integers in the PowerPivot window to get what you need.

Please keep me posted, I am quite interested in how this works out. I've never done anything with "time of day" as a measure but I am sure it's just a matter of time before I encounter it too.

I was trying to remember myself as to how I started it since it has been a while since I built it. But in other words, it was useful to have MIN MAX next to people making calls, this way you have their first and last calls for the day or week.

I also noticed that neither pivot table I had on that sheet would accept the field.
Then I noticed something odd. The field list (show field list) said "Pivot Table" not "PowerPivot". There were also a bunch of calculated fields listed first, instead of just the different Powerpivot sources. I rebuilt the table on another sheet and it worked again and that list said "Powerpivot" on the field lists. So somehow it became a regular pivot table? Very confusing. I did add timestamp to the pivot then changed it under the list where you change the calculations and there is a menu for AVG, MIN and MAX. Then just change the format again to remove the date it seems to like to add. I'll go over again tomorrow to make sure it still works after refresh.

 

[powerpivotpro]

Oh! You are seeing the "native" field list, not the PowerPivot field list.

Check here:

http://www.powerpivotpro.com/2010/06/powerpivot-field-list-and-excel-field-list-at-same-time/