NAV fill with last week range in a date field

catiamatos1991
catiamatos1991 Member Posts: 158
in NAV Three Tier
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>')

Best Answers

  • catiamatos1991
    catiamatos1991 Member Posts: 158
    edited 2018-06-13 Answer ✓
    Slawek_Guzek wrote: »
    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);

    And rethink your career path.

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

Answers

  • catiamatos1991
    catiamatos1991 Member Posts: 158
    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)
  • Slawek_Guzek
    Slawek_Guzek Member Posts: 1,690
    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;
    PRINCE2 Practitioner - License GR657010572SG
    GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
  • catiamatos1991
    catiamatos1991 Member Posts: 158
    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"?
  • Slawek_Guzek
    Slawek_Guzek Member Posts: 1,690
    catiamatos1991 wrote: »
    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;
    PRINCE2 Practitioner - License GR657010572SG
    GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
  • catiamatos1991
    catiamatos1991 Member Posts: 158
    I want to get the first day of the last week
  • Slawek_Guzek
    Slawek_Guzek Member Posts: 1,690
    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);

    And rethink your career path.
    Slawek Guzek
    Dynamics NAV, MS SQL Server, Wherescape RED;
    PRINCE2 Practitioner - License GR657010572SG
    GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
  • catiamatos1991
    catiamatos1991 Member Posts: 158
    edited 2018-06-13 Answer ✓
    Slawek_Guzek wrote: »
    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);

    And rethink your career path.

    Thanks, it worked. My career path? I didn't understand
  • Slawek_Guzek
    Slawek_Guzek Member Posts: 1,690
    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;
    PRINCE2 Practitioner - License GR657010572SG
    GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
  • kriki
    kriki Member, Moderator Posts: 9,121
    [Topic moved from 'NAV/Navision Classic Client' forum to 'NAV Three Tier' forum]

    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!

Categories