Question

Average of equipment date in pivot table

  • 5 November 2023
  • 6 replies
  • 82 views
R
Rank

If I add the export the Equipment GI to Excel (FS2050PL), I am able to take the ManufacturerModelID and the Registered date in excel and use the AVERAGE on the date field to average the date across the machine description to get the average age of those machines.

I had to add these fields to the results grid first

When I try do the same AVERAGE in either a pivot table or applying a pivot filter it doesn't do the average of the field like it does in Excel. It has no problem doing the COUNT option on the same selection.

Any ideas on the way to get it to do this correctly?

 

6 replies

dcomerford
Rank
Userlevel 7
Badge +12

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

Dermot Comerford
R
Rank

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?

 

dcomerford
Rank
Userlevel 7
Badge +12

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

Dermot Comerford
R
Rank

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.

 

 

1 Attachment
Chris Hackett
Rank
Userlevel 7
Badge

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

Chris ~ Let us know how we're doing: https://forms.office.com/r/xmJUdZ1QE5 - A member helped you, please *Like* their post. A member provided an answer to your question, please mark it as *Best Answer*.
D
Rank
Userlevel 4
Badge +1

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.

Reply


Terms & ConditionsCookie settings
About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved