Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

NAV fill with last week range in a date field

Posts: 92Member
I'm trying to figure how to get the last week range to pass to a date field. I'm using the page 408 G/L Balance by dimension and my goal is to fill in the page open the column "DateFilter" with the last week until today (05/06/2018..today)

I tried to use the CALCDATE function and the option "CW" for the expression but I can't find the solution.. ('<-CM - %1W>')

• Posts: 92
FirstDate := CALCDATE('<-CW-1W>', TODAY);
or
FirstDate := CALCDATE('<-1W-CW>', TODAY);
or
FirstDate := CALCDATE('<CW-2W+1D>', TODAY);
or
FirstDate := CALCDATE('<-2W+CW+1D>', TODAY);

Thanks, it worked. My career path? I didn't understand

• Posts: 92Member
but that will result in 10 for the first day and 16 for the last right? I want of the last week (3 to 9)
• Posts: 1,554Member
edited 2018-06-12
CALCDATE expressions work sequentially in order of specification:

CALCDATE('<+CM-CW>', SomeDate) is equal to CALCDATE ('<-CW>'), CALCDATE('<+CM>', SomeDate) ).

NAV first calculates the first part of the expression CM (as if it was calculating the inner CALCDATE(+CM...)) - arriving at the end of the month, and then using just calculated end of month date calculates -CW expression arriving at the last Monday in a month given by the SomeDate (looking backwards it is the first Monday from specified date).

On the top of that (all of this is described in the CALCDATE function documentation:

-1W 'moves' the date one week back.

Now combine the above to build an expression which will give you a Monday one week back from the Monday in current week.

Slawek Guzek
Dynamics NAV, MS SQL Server, Wherescape RED;
GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
• Posts: 92Member
edited 2018-06-13
So I'm getting the time range from 10/06/18 to 17/06/18 but my goal was to get the first period from last week.

FirstDate := CALCDATE('<CW-1W>', TODAY); //Monday
LastDate := CALCDATE('<+CW>', TODAY);//Sunday

How can I assign this range to my field "DateFilter"?
• Posts: 1,554Member
FirstDate := CALCDATE('<CW-1W>', TODAY); //Monday
CW - the END of the current week, Sunday, then -1W move one week back = last Sunday. That's more like the end date you are after, isn't it?
Slawek Guzek
Dynamics NAV, MS SQL Server, Wherescape RED;
GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
• Posts: 92Member
I want to get the first day of the last week
• Posts: 1,554Member
FirstDate := CALCDATE('<-CW-1W>', TODAY);
or
FirstDate := CALCDATE('<-1W-CW>', TODAY);
or
FirstDate := CALCDATE('<CW-2W+1D>', TODAY);
or
FirstDate := CALCDATE('<-2W+CW+1D>', TODAY);

Slawek Guzek
Dynamics NAV, MS SQL Server, Wherescape RED;
GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
• Posts: 92Member
FirstDate := CALCDATE('<-CW-1W>', TODAY);
or
FirstDate := CALCDATE('<-1W-CW>', TODAY);
or
FirstDate := CALCDATE('<CW-2W+1D>', TODAY);
or
FirstDate := CALCDATE('<-2W+CW+1D>', TODAY);

Thanks, it worked. My career path? I didn't understand
• Posts: 1,554Member
It seems to me that you did not try to understand and learn what CALCDATE expressions do, you were only after made ready solution - a line of code which can be copied and pasted.

Programming is about thinking and understanding, and, first and foremost, persistent, never ending learninig. Programming is not about copying lines of code from forums or other sources. If you are not into thinking and learninig too much maybe programming is not the right job choice for you.
Slawek Guzek
Dynamics NAV, MS SQL Server, Wherescape RED;