Calculations using FinPeriod - Acumatica Generic Inquiries & Pivot Tables

RE: Calculations using FinPeriod

Rick Piester — Wed, 25 Jan 2023 16:52:16 +0000

@ncantral Thank you! That's an excellent idea.

RE: Calculations using FinPeriod

ncantral — Wed, 25 Jan 2023 16:46:48 +0000

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.

3076

Calculations using FinPeriod

Rick Piester — Wed, 25 Jan 2023 15:58:08 +0000

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(=,Cint(),0)

=iif(=-1,Cint(),0)

=iif(=-2,Cint(),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:

3075