Skip to main content

Notifications

Microsoft Dynamics NAV forum
Suggested answer

column layout formula

Posted on by 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?

pastedimage1595858955974v1.png

  • Suggested answer
    ManishS Profile Picture
    ManishS 6,578 on at
    RE: column layout formula

    This is what you need to do for getting variance for each month.

    New-Bitmap-Image.jpg

  • ManishS Profile Picture
    ManishS 6,578 on at
    RE: column layout formula

    A1-B1 means the difference will be shown for only Jan Month.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    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

    pastedimage1596620109516v1.png

  • Suggested answer
    CarmenNunes Profile Picture
    CarmenNunes on at
    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 Profile Picture
    Community Member Microsoft Employee on at
    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 Profile Picture
    CarmenNunes on at
    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 Profile Picture
    Community Member Microsoft Employee on at
    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 Profile Picture
    Community Member Microsoft Employee on at
    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 Profile Picture
    CarmenNunes on at
    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

    pastedimage1595867986524v4.png

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

    pastedimage1595867939868v3.png

    Hope  this can help you.

  • ManishS Profile Picture
    ManishS 6,578 on at
    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.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Anton Venter – Community Spotlight

Kudos to our October Community Star of the month!

Announcing Our 2024 Season 2 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Dynamics 365 Community Newsletter - September 2024

Check out the latest community news

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,524 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 228,469 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans