Questions Calculations using FinPeriod

Acumatica Generic Inquiries & Pivot Tables

Last Post by Rick Piester 3 years ago

3 Posts

2 Users

1 Reactions

1,761 Views

RSS

Posts: 21

Rick Piester

Topic starter

January 25, 2023 11:58 am

(@rpiester)

Eminent Member

Joined: 3 years ago

I have a Generic Inquiry where I need to show the most recent 6 months sales of each item based on a defined ending period.

To collect the data for each month I use the following formula:

=iif([INItemSiteHist.FinPeriodID]=[EndingFinPeriod],Cint([INItemSiteHist.TranPtdQtySales]),0)

=iif([INItemSiteHist.FinPeriodID]=[EndingFinPeriod]-1,Cint([INItemSiteHist.TranPtdQtySales]),0)

=iif([INItemSiteHist.FinPeriodID]=[EndingFinPeriod]-2,Cint([INItemSiteHist.TranPtdQtySales]),0)

and so on

"EndingFinPeriod" is a parameter selected when the user runs the inquiry.

This works when all of the months are within the year in the EndingFinPeriod. If 12-2022 is selected, it shows the last 6 months of 2022. But if 01-2023 is selected, it only shows that one month.

Is there an easy way to "do math" within a formula on FinPeriods? Or do I need to dissect the fin period month and year?

A bonus question: Is there still no way in a Generic Inquiry to use a formula to vary what displays in the column caption?

An example result is below:

2 Replies

Posts: 24

ncantral

January 25, 2023 12:46 pm

(@ncantral)

Eminent Member

Joined: 5 years ago

I was just playing with a similar query. My solution was to join INItemSiteHist.finPeriodID to FinPeriod.masterFinPeriodID. This gives you access to the start and end dates for each period and you can then use functions like DateAdd() to filter results.