Skip to main content
Solved

Usage Current vs Prior Generic Inquiry/Pivot Table

L
Freshman I
Forum|alt.badge.img

Hello, 

 

I am trying to create a GI that shows inventory usage (sales and issues) for a pivot table that will display the current 12 months vs prior past 12 months.  My last activity link does not seem to be displaying data for the last activity period.  I am also trying to create a formula to group periods by current and prior which I can use as columns on a Pivot Table for qty totals. Something like switch(month(today()),'1',month(today()-1),'1') then when it gets far enough back switch(month(today()-12,'2',month,today()-13,'2') but I am unsure of how to apply this in the GI. Can anyone provide help on this?  GI attached.

 

thank you! 

Best answer by lauraj46

Hi ​@lbrown57 ,

For the period groupings, you could use a formula like this:

=Floor((DateDiff('m',[MasterFinPeriod.StartDate],today()))/12)+1

I’m not sure about the last activity.  I ran your query in my demo data and I did see values but not sure if they are what you are expecting.  To troubleshoot this, I would suggest creating a simple GI with only the INItemCostHist table to verify that this data is what you would expect for a specific InventoryID, AccountID and financial period.  Also confirm that you are selecting the appropriate data fields.  If that looks good, then review INItemCostHistByPeriod.lastActivityPeriod to validate that that is what you expect.

Hope this helps!

Laura

View original
1 Attachments
Did this topic help you find an answer to your question?

2 replies

lauraj46
Captain II
Forum|alt.badge.img+8
  • lauraj46
  • Captain II
  • 604 replies
  • Answer
  • March 26, 2025

Hi ​@lbrown57 ,

For the period groupings, you could use a formula like this:

=Floor((DateDiff('m',[MasterFinPeriod.StartDate],today()))/12)+1

I’m not sure about the last activity.  I ran your query in my demo data and I did see values but not sure if they are what you are expecting.  To troubleshoot this, I would suggest creating a simple GI with only the INItemCostHist table to verify that this data is what you would expect for a specific InventoryID, AccountID and financial period.  Also confirm that you are selecting the appropriate data fields.  If that looks good, then review INItemCostHistByPeriod.lastActivityPeriod to validate that that is what you expect.

Hope this helps!

Laura

Laura - https://www.linkedin.com/in/laura-jaffe-965a411a/ ~ If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
Chris Hackett
L
2 people like this
  • Chris Hackett
    Chris Hackett
  • L
    lbrown57
L
Freshman I
Forum|alt.badge.img
  • lbrown57
  • Author
  • Freshman I
  • 20 replies
  • April 3, 2025

Thank you! That formula worked for what I needed. 

lauraj46
Chris Hackett
2 people like this
  • lauraj46
    lauraj46
  • Chris Hackett
    Chris Hackett

Reply


Most helpful members this week

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie 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