0

How to define Next-day(sysdate)

Hi,

Please let me know how to define below formula in Incorta.

TRANSACTION_DATE  BETWEEN  next_day(sysdate-14,'sunday')  AND  next_day(sysdate-7,'saturday') 

 

Regards,

Hari.

7replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Hi Hari,

    Formula shown above will be similar to this formula (assuming "order"  is a table in schema "sales"):

    and (

        daysBetween (

            $currentDate,

            sales.order.Date

        ) >= 7,

        daysBetween (

            $currentDate,

            sales.order.Date

        ) < 14

    )

    Like
  • Hi Anurag,

    Thank you for your replay. Formula working fine, can we add days(Sunday, Saturday) in the formula?

    Regards,

    Hari.

    Like
  • I guess that you would like to get data from last week. 

    if(

        between(

            <schema>.<table>.TRANSACTION_DATE,

           addDays(weekEndDate(addDays($currentDate,-14 )    ),    1),

           weekEndDate(addDays($currentDate,-7))),

        'T',

        'F'

    )

    Incorta provides weekStartDate() as well.  Please note that a week starts on Sunday and ends on Saturday.

    Like 1
      • Marc Paige
      • GC Services
      • Marc_Paige
      • 1 yr ago
      • Reported - view

      Dylan , Where are these functions defined? I looked in the documentation. I am looking at version 4.7.

      Like
      • KHK
      • Wipro
      • Hari_K
      • 1 yr ago
      • Reported - view

      Dylan Wan Thank you for your replay.

      I am creating this variable at report level and am getting " Formula cannot be saved.
      Undefined function 'weekEndDate'" error.

      weekEndDate and weekStartDate functions not supporting. Could you please help on this.

      Like
      • KHK
      • Wipro
      • Hari_K
      • 1 yr ago
      • Reported - view

      KHK Hi Dylan,

      Could you please let me know where these functions defined?

      Like
    • KHK Marc Paige  Sorry I found that I tested this in a pre-release version of Incorta.  between is available in 4.7.2, weekStartDate and weekEndDate will be  probably available in 4.8 if it passes all tests.

      Like
Like Follow
  • Status Answered
  • 1 yr agoLast active
  • 7Replies
  • 86Views
  • 4 Following

Product Announcement

A new community experience is coming! If you would like to have beta access to provide feedback, please contact us at community@incorta.com.