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

column layout formula

(0) Share

Report

Posted on by Community Member Microsoft Employee

I've been struggling with accounts schedules in NAV 2009. I've read some instructions in order to get the comparison period formula column, field 14 in the column layout table. Is this available to add in all versions of 2009 or is it possible that this is not an option in some versions and you only have comparison date formula?

As I only have comparison date formula for now, I've been having issues in creating a column layout which gives 12 months actuals including YTD and 12 months budget including YTD. I have managed to find formula to give each month but don't know how to get a current month variance for actuals v budget. Any idea?

All responses (10)

Answers (0)

Suggested answer

ManishS 6,578 on at

Like (0)

Report

RE: column layout formula

This is what you need to do for getting variance for each month.
ManishS 6,578 on at

Like (0)

Report

RE: column layout formula

A1-B1 means the difference will be shown for only Jan Month.
Community Member Microsoft Employee on at

Like (0)

Report

RE: column layout formula

thanks for your response Carmen but can you check my attached column layout and let me know how to amend to get it to work. Its the highlighted line that doesn't work - how to get the actuals v budget variance for the month so If I view my account schedule at 30th Jul then I get the variance for that month and I also get the YTD variance in another column
Suggested answer

CarmenNunes on at

Like (1)

Report

RE: column layout formula

Hi Simone,

I just take a look in previous Manish suggestion and in fact it should work if you define different codes for each columns GL Entries and GL Budget Entries instead using always "A" and "B".

Then you have to add an additional line to get the difference between those lines without entering a comparison Date formula.

Hope this can help you.
Community Member Microsoft Employee on at

Like (0)

Report

RE: column layout formula

Hi Carmen

That would be really helpful. Even just for 3 separate months as I'm at a loss!
Suggested answer

CarmenNunes on at

Like (0)

Report

RE: column layout formula

Hi Simone,

That's true my sample is YTD but also depends the date filter we use for it.

I was just trying to show how you can add some additional column to get average % between both amounts.

I will try to repro a similar case as yours.
Community Member Microsoft Employee on at

Like (0)

Report

RE: column layout formula

Hi Carmen, I'm trying to get 12 month columns for actuals with a YTD, then 12 month columns for budget with YTD, then a current month variance column and a YTD variance column. I get everything to work apart from the current month variance. Your example looks like a YTD example
Community Member Microsoft Employee on at

Like (0)

Report

RE: column layout formula

Hi Manish, thanks so much for response. Your suggestion on variance only works for the month of January then the variance becomes the same as YTD. Any other ideas?

I'm just trying to get 12 month columns for actuals with a YTD, then 12 month columns for budget with YTD, then a current month variance column and a YTD variance column. I get everything to work apart from the current month variance
Suggested answer

CarmenNunes on at

Like (0)

Report

RE: column layout formula

Hello Simone,

Not sure if is this what you are looking for, but you can add some additional Columns Lines to have the Average % between Real Entries and Budget.

Here is a sample of Column Layout

and the result that you can get when running account schedule for a range of dates

Hope this can help you.
ManishS 6,578 on at

Like (0)

Report

RE: column layout formula

Variance for which month you are looking at, what does variance A-B represents to.

You need to Enter Column No. A1,A2 and so on till 12 for actuals & for Budget entries B1,B2 and so on.

Now in your Column No. 27 enter A1+A2 till A12 minus B1+B2 till B12 to get the variance.