Comparison Period Formula Symbols

Business Central/NAV User Forum
1

Where is the reference for the comparison period formulas? The help section states that you can manipulate data with “acceptable” symbols but doesn’t show them. Any help would be appreciated Many thanks, Jeff Kaufman United States

2

You can enter for example -1Y To indicate the same period but one year earlier. Typical date comparison formula which entered for example, in recurring journals are: 3m - to indicate 3 months 1d - 1 day 1w - 1 week and so on So to use the Comparison Period Formula in account schedule column format form, enter the number then d to indicate day, w for week or y for year. Remembering that formula that you enter calculates the comparison period in relation to the period represented by the date filter on the report request. Hope this makes sense? _________________________ Wendy O’Connor Practice Leader woconnor@csbsystems.com CSB Systems Calgary, Alberta T2R 1L9 T:(403) 233-2955 x 224 F:(403) 233-2957

3

For more information, look under in the Help File for Subject - How to Use Date Formulas. There area a few examples in there to get you on your way Cheers Darren Bezzant Development Specialist dbezzant@csbsystems.com CSB Systems 1560 - 333 11th Avenue SW Calgary, Alberta T2R 1L9 Tel: (403) 233-2955 Fax: (403) 233-2957

4

I need to set up an analysis report using last year’s sales Qty and amount along with this year’s sale Qty and amount. I filled in -1FY[1…12] or -12p in comparison period filed in order to get last year’s date but nothing shows up. How can go back to retrieve last year fiscal year’s data? Thanks,

5

Hi trin, you can use the following formulas: first day of last fiscal year: CY - 2Y +1D last day of last fiscal year: CY - 1Y the ‘C’ in ‘CY’ stands for Current, and don’t forget, that the DateExpressions are different, depending on the language you use. Cheers Daniel

6

Also remember that CY means the end of the current year, whereas -CY means the begining of the current Year.

7

Also if using weeks, (eg CW or -CW) keep in mind that ALL versions of Navision, even GB, US and AU all count Monday as day one, or the first day of the week, not (as we would expect Sunday). So -CW = Monday of the current week CW = this coming Sunday -CM+WD1 = the first Monday of the Monday, not the first Sunday… etc. PS to get the range ofdates for last year, I prefer -CY-1Y to CY-1Y

8

Thanks for the help. I have another question. I need to annualize sales Qty. which is using YTD sales Qty/ month*12. How can I get the system to count the number of YTD months? Thanks again.

9

Hi All,

I need help on setting the columnar format in the Accounts Schedule for P&L comparison.

I tried using the following: Blank for Jan, 1M for Feb, 2M for Mar and so on… What I got was regressive cumulative figures.

Need help!

Desmond (Singapore)

10

Hi Desmond, and welcome to forum!

Its really so, that Date and Comparison Date formula accepted symbols are badly documented, plus, these change when you change language.

Besides, experimenting and checking if result is as expected is almost the only way how to set all this up in Acc.Schedules. This has been discussed here already, do some search with Account Schedule as keyword, you fill find many samples.

for begining, check this thread http://dynamicsuser.net/forums/p/25827/139995.aspx Symbols there are valid for English language, if Navision is switched to, say, German or Spain, letters will differ.

11

Hi !

Can you please tell me how to get the last year’s amounts per month? I tried -1FY (comparasion data formula) and it works just fine but i don’t know what formula I need to use to get also amounts per each month? (last year)

Please help! thank youuuu!

12

What you can do is create multiple columns, each with their own comparison date formula.

So Column C010 is -1M, column C020 is -2M etc.

What this does restrict is the usefulness of the date filters on the account schedule form. So, depending on what you want to see, you will either need to set the date to be the start, or the end of the finanicial year, and then have the columns going forward from the start +1M, +2M etc. or backward from the end -1M, -2M etc.

The other consideration with this approach is the physical real-estate on the form. Unless you have a large 19 inch plus monitor, you will never see all of the column you want to see on the form and will always resort to the export to excel function (either from the form, or via the reports)

hope this helps

Kryt.