Hi Rui,

No no. I want to get all the BankMovements of the week from Monday to Sunday.

How I wrote in the post:

For example, now I am on 13 January 2021 and I did a BankMovement,  yesterday I did another one and tomorrow and the next days I'll do more BankMovements. So, I want to write a query to catch the data "BankMovement.Date" between Monday and Sunday (11-17 January) .

So on the next Sunday - 18 January 2021 - I'll start a new week and I only want to fetch the data between 18-24 January.

I hope you understand what I want to do.

Any questions please feel free to tell me.

Thanks.

FC

Hello again Fábio,

I think that's going to be a bit more complex then. Unfortunately there is not built-in function that gives you that information, so you need to implement it out of the box.

Since you want to consider a week always as Monday - Sunday, you will need to calculate these 2 days based on your current date.

• Calculate the Monday of the week based on the current date:

DateTimeToDate(
    If( DayOfWeek(CurrDate()) = 1, CurrDate(),
        If( DayOfWeek(CurrDate()) = 2, AddDays(CurrDate(), -1),
            If( DayOfWeek(CurrDate()) = 3, AddDays(CurrDate(), -2),
                If( DayOfWeek(CurrDate()) = 4, AddDays(CurrDate(), -3),
                    If( DayOfWeek(CurrDate()) = 5, AddDays(CurrDate(), -4),
                        If( DayOfWeek(CurrDate()) = 6, AddDays(CurrDate(), -5),
                            If( DayOfWeek(CurrDate()) = 0, AddDays(CurrDate(), -6),
                                CurrDate()
                            )
                        )
                    )
                )
            )
        )
    )
)

Basically, I'm using DayOfWeek() and AddDays() built-in functions to calculate the corresponding Monday. As an example, one of the conditions is:

If ( DayOfWeek(CurrDate()) = 3, AddDays(CurrDate(), -2)

This means that if your CurrDate() is a Wednesday, DayOfWeek() function will return 3, so you have to remove 2 days to get Monday. If your CurrDate() is a Friday, DayOfWeek() function will return 5, so you have to remove 4 days to get Monday. The rest of the conditions are the same, but for the several days of the week.

• Calculate the Sunday of the week based on the current date:

DateTimeToDate(
    AddDays(
        If( DayOfWeek(CurrDate()) = 1, CurrDate(),
            If( DayOfWeek(CurrDate()) = 2, AddDays(CurrDate(), -1),
                If( DayOfWeek(CurrDate()) = 3, AddDays(CurrDate(), -2),
                    If( DayOfWeek(CurrDate()) = 4, AddDays(CurrDate(), -3),
                        If( DayOfWeek(CurrDate()) = 5, AddDays(CurrDate(), -4),
                            If( DayOfWeek(CurrDate()) = 6, AddDays(CurrDate(), -5),
                                If( DayOfWeek(CurrDate()) = 0, AddDays(CurrDate(), -6),
                                    CurrDate()
                                )
                            )
                        )
                    )
                )
            )
    ), 6)
)

The rational is basically the same to find Monday, but I'm adding 6 days at the end of the calculation to get the corresponding Sunday.

Since these expressions are very long and hard to maintain, I hardly suggest that you don't use them directly in your query.

Please consider to create functions in order to abstract this entire logic.

For example, you can create 2 functions:

• CalculateMondayOfTheCurrentWeek that returns the Monday as a Date (first expression)

• CalculateSundayOfTheCurrentWeek that returns the Sunday as a Date (second expression)

Then back to your query, your expression should be something like:

BankMovement.Date >= CalculateMondayOfTheCurrentWeek()
and
BankMovement.Date <= CalculateSundayOfTheCurrentWeek()

I did some unit tests for some scenarios and it seems to be working properly. But I strongly advise you to test this logic for all possible scenarios (examples: 2 different months in the same week, 2 different years in the same week, etc.).

Hope that this helps you!

Kind regards,

Rui Barradas

Rui,

Thanks for the explanation, it's perfect :)

I did what you wrote and works perfectly!!!

It's solved.

Thanks for your help.

Have a great day.

Best regards,

FC

Hi Rodrigo,

I hope you are fine.

Thanks for your suggestion.

I think your solution doesn't work because I have one Aggregate and not one Advanced Query.

I appreciate your help!

Thanks in advance.

Best regards,

FC

Hi Lennart,

I hope you are fine.

Thanks for your suggestion.

My app is a reactive app. I think that function is for web traditional or not?

I'll do what you suggest and I'll give you feedback if it's work or not.

Thanks in advance.

Best regards,

FC

Hi Fabio,

I missed the Reactive tag. My solution indeed used server actions to get where you want to be.
You can copy the server action to a client action. Then you'll get:

AddDays(Date,If(DayOfWeek(Date)=0,-6,1-DayOfWeek(Date)))

It's basically what @Rui Barradas said, but a little shorter.

Goodluck!
Lennart