Small and medium business | Business Central, N...

Account Schedule Column Layout for QTD (Quarterly to Date)

(0) Share

Report

Posted on by AngieZ

Can we set up Quarterly to date as one column in Account Schedule? It seems that Account Schedule can't handle QTD flexibly. Any advice is appreciated!

Our client has been using Management Report for financial reports and now try to move to Business Central. For the QTD column, in management reporter, it is set up using 15 columns:

C1: Base period

C2: Base period - 1

C3: Base period -2

C4 - C15: Jan - Dec and define each month's QTD calculations.

I have the same question (0)

All responses (8)

Answers (0)

Suggested answer

YUN ZHU 95,595 Super User 2025 Season 2 on at

Like (0)

Report

Hi, hope the following will help.

https://dynamicsnavfinancials.com/account-schedule-formulas/

Thanks.

ZHU

Was this reply helpful? Yes No
AngieZ 50 on at

Like (0)

Report

Hi Zhu,

Thank you for your reply! The suggested link are four fixed columns to show Q1 - Q4. However, what we are looking for is one column called QTD showing its QTD value. For example, fiscal year is April to March. If I run report as of 5/31/2022, QTD column shows net change between 4/1/2022..5/31/2022. If I run report as of 7/31/2022, QTY column shows net change between 7/1/2022..7/31/2022. Is such flexible expected result supported in Account Schedule?

Thank you!

Angie

Was this reply helpful? Yes No
Dallefeld 211 User Group Leader on at

Like (0)

Report

Can you clarify what you are looking for in each of the column?

Was this reply helpful? Yes No
AngieZ 50 on at

Like (0)

Report

Hi Kim,

For my PL and BS report, besides from the regular columns (which is not in the scope of this discussion), I would also like to have one column named "QTD" to show the Quarterly to date periodic value.

For example, if the company fiscal year is from April to March. Its quarterly starting date are 4/1, 7/1, 10/1 and 1/1.

That is to say, below is my expected QTD result when I run PL report for different months:

Run for April: QTD = Net change in 4/1..4/30

Run for May: QTD = Net change in 4/1..5/31

Run for June: QTD = Net change in 4/1..6/30

Run for July: QTD = Net change in 7/1..7/31

Run for August: QTD = Net change in 7/1..8/31

...

This can be easily achieved by Jet report and Manage reporter, where we can achieve it by writing multiple hidden columns for calculation. However, I wonder if Account Schedule can achieve this as well. I did not find any period formula to support this.

Thank you!

Angie

Was this reply helpful? Yes No
Suggested answer

Dallefeld 211 User Group Leader on at

Like (0)

Report

Try this.....

Run for April: QTD = Net change in 4/1..4/30 - data comparison formula FY[1]

Run for May: QTD = Net change in 4/1..5/31 - data comparison formula FY[1..2]

Run for June: QTD = Net change in 4/1..6/30 - data comparison formula FY[1..3]

Run for July: QTD = Net change in 7/1..7/31 - data comparison formula FY[4]

Run for August: QTD = Net change in 7/1..8/31 - data comparison formula FY[4..5]

When run your report, always start with the date filter of 0401 and end with the date to report through. Example 04/01..05/31, 04/01..07/31

Was this reply helpful? Yes No
AngieZ 50 on at

Like (0)

Report

Hi Kim,

For this solution, I need to update my data comparison formula every month before running the report. It can be difficult for end users to follow though. It can be one of the workaround if Account Schedule could not support Quarterly to date formula flexibly?

Thank you for your advice!

Angie

Was this reply helpful? Yes No
Dallefeld 211 User Group Leader on at

Like (0)

Report

So you are rotating columns? If you make your columns static, the above should work for you.

Was this reply helpful? Yes No
AngieZ 50 on at

Like (0)

Report

I am looking for the possibility of setting up only one column called "QTD" to show QTD according to the month I run for flexibly, not 12 static columns for each month (unless I can only show the column of the month I run but account schedule can't do "if else" syntax).

Was this reply helpful? Yes No