Average of equipment date in pivot table

Are you calculating the Age in Days for each record in the GI itself using something like this to Results Grid.

DateDiff(d, [FESquipment.RegisteredDate], Today() ) as the DataField

Hi dcomerford, Thanks for your response. I have followed your logic and correctly calculated the average number of days now in the pivot using =DateDiff('d',[FSEquipment.RegisteredDate], Today()) in the GI.

How do I now convert the pivot table result back to a date?

For example this is the result in the pivot table, I have 12 Melitta TS with an average age of 2,568 days from today. I want to put this on a dashboard but instead of displaying the number of days in the dashboard I want to show Melitta TS, 12, 26 October 2016. Can this be done?

You can add a number to a date by using so in case below add 3568 days to today date and display the date

DateAdd(Today(), d, 2568 )

You could use a calculation to get the ‘2568’ but unsure if this is a Total Average which may not be possible would need to see the GI you are using

Hi dcomerford, I have attached the XML file, I am not sure if the tabs (Pivots) come through on the XML, its the Equipment average age I am trying to work out, if I replace the Registered Date field with the Days Old field it will calculate the average days of the machine correctly, but I need this to display the average date.

Hi @ryonxan were you able to find a solution? Thank you!

I am having the same issue.  I am trying to display MIN or MAX of dates in a pivot table Values, but Acumatica does not handle this.  If I dump the GI to Excel and create the exact same pivot table, it works.  Looks like Acumatica simply does not handle dates in pivot tables.