68 - Account Schedules in Dynamics NAV 2017 - Part 3             

We keep going on the account schedules, so it's becoming a trend to me to create a few videos about the same topic, especially if that topic has a lot of things around it and account schedules definitely do.

Today what we are going to do is continue with the account schedule we created on the previous video.

I’m actually logged in as the account manager so the analysis tab on the ribbon there is account schedules I get a list of the account schedules, and I gonna go into the new one that I created, its called profit for retail, and I’m just gonna edit that one, and that one actually is looking at just the profit of our retail operation and if I look at the overview, I kinda get the sales of retail, cost of retail and then the gross profit.

I had created a cloud layer which was called Period and year to date, that one gives us the net change and year to date. So if I look at a month right here and I say 2017 November, I can see that November had 143,00 and year to date is 1.3 million.

What I wanna highlight right now is another column layer that I find very interesting, we don't really have anything similar in the standard column layout that comes with the system, but what I like to see is like four quarters, now the current quarter that I'm actually looking at and then previous quarters back, so just lined up, to kinda see a trend of sales and cost and gross profit.

How do we do that?, so I’m gonna create a new column layout and hit new here and we are going to called it 4QS for four quarters, and in here, I'm just gonna hit edit setup and basically the highlight of this video is highlighting the formula here, we do comparison date formula right here, we can actually use that to compare other date ranges, so the first one I'm just gonna leave as the CQ current quarters, and do nothing in the date formula, and the second one I'm gonna call it negative 1 quarters -1Q, last quarter, so I actually put negative one because we are going to be counting backwards.

Last quarter makes sense but there is last quarter that is the last quarter before that, so there’s no really good word for that, so negative 1 is fine. My background being math anyways like that.

So will just put -1Quartes as well, and that's gonna be net change but in the comparison formula we are going to use a date formula that works for NAV, so NAV you can manipulate dates with text string, and you can read up about that but in this case that would be a - 1Q, probably -1 works but I like to have a 1 in front that's just a little bit more clear. And then we keep on going -1Q, -2Q, and -3Q, so these are 4 quarters looking backward, I hit ok, that's fine, 4 quarters, just hit ok.

Now you can see I’m looking a current quarter and then quarters behind, now I have to be careful here, my current date filter is a month it that doesn't make sense that that is the current quarter, so I have to set the view to be a Quarters and then things work out nicely.

If I'm looking at a particular quarter, in these case I'm looking at the last quarter in 2017 that is the current quarter right here, and then I can see the quarter before that quarter before that, etc all the way back. So I’m looking at the entire year right here by quarters and I can see how my profit was really good in the second quarter of the year and not as good for the other 3 quarters, and we can see that’s probably because the cost was way lower.

You can read a lot into this, but essentially this highlights a very powerful feature of comparing dates on to the line here, you can set them up any way you want and have very complicated date formulas.