Forum Discussion
Time-Axis doubt
Dear Experts,
I have a data as attached and below, I want to have the Time-diff in Y-Axis, but seems none is plotted:-
Could you please share what wrong I am doing here.
Thanks & Regards
Anupam Shrivastava
Yes, we can't sum datetime. We could find, for example, min or max for it. Use source as numbers, sum and apply datetime format to resulting field.
6 Replies
- anupambit1797Iron Contributor
Thanks SergeiBaklan​ , I added that option "Add this data to the Data Model" during Pivot, but get below error:-
While I already changed the Data type to Custom hh:mm:ss:.000
Br,
Anupam
- SergeiBaklanDiamond Contributor
Yes, we can't sum datetime. We could find, for example, min or max for it. Use source as numbers, sum and apply datetime format to resulting field.
- SergeiBaklanDiamond Contributor
In addition. Cached PivotTable round milliseconds (as well as formula bar,
)
One workaround is to work with data model as Detlef explained. Another way is to apply to Time-diff column General format, create cached PivotTable and apply to the result custom number format with milliseconds. (or created copy of TimeDiff column in the table formatted as General and build PivotTable using it).
See also Pivot Table Values not matching Source values (Max Date) | Microsoft Community Hub
- Detlef_LewinSilver Contributor
The values in your pivot table are all 0. It seems that a regular pivot table cannot deal with that small numbers.
Try a data model pivot table instead. It works for me.
- anupambit1797Iron Contributor
Thanks Detlef_Lewin​ for your response, could you please share the steps along with the attached worksheet .
Thanks & Regards
Anupam
- Detlef_LewinSilver Contributor
Go to Insert > PivotTable. In the "Create PivotTable" dialog box, select your table range and be sure to check the box that says "Add this data to the Data Model" before clicking OK.
(Copied from a chatbot answer)