Answer

Usage Current vs Prior Generic Inquiry/Pivot Table

Forum|Forum|8 months ago
March 26, 2025
2 replies
78 views

lbrown57
Freshman II

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

+8

lauraj46
Captain II
Answer
Forum|Forum|8 months ago
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*.