Account Schedule - Column

(0) Share

Report

Posted on by Ian Fletcher

2,445

Hi, we have Nav 2009 R2, I am trying to setup Account Schedules to report by Dimension Department, the Dimension is setup, the Account Schedule is setup with the correct GL Codes & Dimension, it is the columns I am having trouble with.

I want to compare actuals to budgets for last year (2013) & for the current year (2014) but I cant seem to get the formulas correct for 2014. I have seen help that says use FY[1] - but when I do this I get an error - your entry of FY[1] is not an acceptable value for Comparison Date Formula

*This post is locked for comments

I have the same question (0)

All responses (9)

Answers (0)

Alex Chow 4,481 on at

Like (0)

Report

For the comparison date formula, you have to put in the date formula you want to compare it against. For example, if your run your report with Date Filter of 12/01/13..12/31/13, and if you want to compare it with the same period in the prior year, then you have to setup -1Y.

If you want to use the period then it goes to the Comparison Period Formula column.

Was this reply helpful? Yes No
Ian Fletcher 2,445 on at

Like (0)

Report

I get this to work for looking at 2013, but not for 2014, what do I need in my columns if I want to review this as a budget analysis?

I was hoping for the 12 months for 2014 as columns, then the GL codes going down & then the figures to show Actual costs for each month compared to Budget amounts for each month. When I have run the print I have used date filter of 01/01/14..31/12/14, January looks righ I think, but currently Feb has the same amounts as January which is incorrect & the same for March, so must be a an error somewhere?

Also I do not have a Comparison Period Formula column? (have checked that it is not hidden, it is also not available in classic?

Was this reply helpful? Yes No
Alex Chow 4,481 on at

Like (0)

Report

It depends on what you set as your Date Filter when you run your accounts schedule report. If you use dates in 2014, then it'll calculate for 2014.

Was this reply helpful? Yes No
Ian Fletcher 2,445 on at

Like (0)

Report

It will only correctly use 2014 if I have the correct formula in the Column layout to tell it which month is January, which is February etc which is what I'm looking for.

If I set the date filter as 010114..311214 have January as net change - formula CM-1 then this seems to give me the expected posted amounts.

for February I have used Comparison Date formula -CY+1M & ticked Keep comparison period scope & I believe this also works, although we do not many amounts posted yet to correctly test this out yet.

Does this look correct?

Was this reply helpful? Yes No
Alex Chow 4,481 on at

Like (0)

Report

You can also use the Comparison Period Formula. Set it has FY[1]. Then it'll use the first period in 2014.

Was this reply helpful? Yes No
Ian Fletcher 2,445 on at

Like (0)

Report

Hi, as said in my first post I tried that & I get the error - your entry of FY[1] is not an acceptable value for Comparison Date Formula

From my second post - Also I do not have a Comparison Period Formula column? (have checked that it is not hidden, it is also not available in classic?

Unless i have done something else incorrectly? Or does another part of Nav need to be setup

Or how do I get Comparison Period Formula?

Was this reply helpful? Yes No
Suggested answer

Aleksandar Totovic 16,765 on at

Like (0)

Report

Comparison Period Formula is field no.14 in Column Layout table. Maybe you have not view on this field by default, but you can check it in table and add to your page/form.

Was this reply helpful? Yes No
Ian Fletcher 2,445 on at

Like (0)

Report

Thank you Aleksandar, unfortunately when I look at Table 334 in Object Design I do not have a Line/Field No 14, it goes from Line/Field No 12 Rounding Factor to Line/Field No 15 Business Unit Totalling?

What are the parameters for me to add this in?

Thanks

Ian

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Ian,

You need to place the FY[1] in the Comparison period formula. Comparison Date formula takes entries such as 1D,2W etc.

Regards

James

Was this reply helpful? Yes No