0

Find in View (Today, Last Week 5 days, Last Month, Last Six Month and Year.)

Need a script that find the info by (Today, Last Week 5 days, Last Month, Last Six Month and Year.)

Somebody helpme.

8 replies

null
    • Rafael Sanchis
    • Rafael_Sanchis
    • 9 mths ago
    • Reported - view
    switch 'Viewing Period (from today)' do
case 1:
    (select 'Booking Time')[Date > date(year(now()), month(now()), day(now()) - 7)]
case 2:
    (select 'Booking Time')[Date > date(year(now()), month(now()), day(now()) - 14)]
case 3:
    (select 'Booking Time')[Date > date(year(now()), month(now()) - 2, day(now()))]
case 4:
    (select 'Booking Time')[Date > date(year(now()), month(now()) - 3, day(now()))]
case 5:
    (select 'Booking Time')[Date > date(year(now()) - 1, month(now()), day(now()))]
default:
    (select 'Booking Time')
end

    I find this, but it's not what I'm looking for,  need (Today, Current Week, Current Two Weeks, Current Year)

      • Fred
      • 9 mths ago
      • Reported - view

       what does your version of the code look like? I would also switch the now() to today() since you don’t need the time data that now() has.

      • Rafael Sanchis
      • Rafael_Sanchis
      • 9 mths ago
      • Reported - view

       

      hi Fred Now this is my Code. But the problem in this example, I should only appear year 2024 but appear 2023 too.

      And on the This week need the current Week 11/Feb to 17/Feb

      • Fred
      • 9 mths ago
      • Reported - view

      The code looks ok to me so I don't know why you are getting records that seem to fall out side the filter. Can you upload a sample DB with no personal info?

      • Rafael Sanchis
      • Rafael_Sanchis
      • 9 mths ago
      • Reported - view

       

      No is a Dummy DB, in Info Table  Thanks 

      Dummy.ninox
    • Fred
    • 9 mths ago
    • Reported - view
     said:
    hi Fred Now this is my Code. But the problem in this example, I should only appear year 2024 but appear 2023 too.

     Ok, well looking at your code closer (which I didn't do very well before). The This Year selection (5) is doing exactly what you ask:

    case 5:
    (select 'Booking Time')[Date > date(year(now()) - 1, month(now()), day(now()))]

    As you can see you are subtracting 1 from the year. So you are asking for all records from (as of this post) 15 Feb 2023 to today.

    If you only want the current year how would you change that?

    Correct, you would remove the - 1.

    The other issue is you are using the month and day of now(). But you want from the beginning of the year. How would you change the month and day to reflect the first month and first day of the year?

    Correct, you would put 1 for each.

    So your code would look like:

    case 5: (select 'Booking Time')[Date > date(year(today()), 1, 1)]
      • Rafael Sanchis
      • Rafael_Sanchis
      • 9 mths ago
      • Reported - view

       

      Well explained Fred Tanks.👍

    • Sam.1
    • 9 mths ago
    • Reported - view

    Hi All,

    I have applied the formula provided by Rafael and Fred’s suggestion for an appointment table view.

    All has worked well. Thank you for that!

    However, when I apply the basic formula with a couple adjustments for future appointments in a separate view it filters dates in the future beyond a week( 2 weeks/month etc).

    Here is an example of the adjustments:

    case 4:

    (select Appointment)[‘Appointment Date' < date(year(now()), month(now()), day(today()) + 14)]

    I would appreciate some suggestions.

    Thanks,

    Sam

Login to reply

Content aside

  • Status Answered
  • 9 mths agoLast active
  • 8Replies
  • 72Views
  • 3 Following