Slice filter by date range

[VIRAJ_MISTRY]

Hello,

I have a slice for my table “Expense Report” with a “Date” column. My employees add their daily expense to it.

I want to filter the data seen in the slice with a date range.

Eg:
If today is 04-03-2021
Then range should be from 21-02-2021 to 20-03-2021.

If today is 04-04-2021
Then range should be from 21-03-2021 to 20-04-2021.

The cycle starts on 21st of every month to 20th of next month.
New cycle starts on 21st of that month.

There is no user input to set the start or end date. This will remain constant.

[Suvrutt_Gurjar]

Welcome to the AppSheet community.

What result would you like on other days, for example

1. If today is 21-02-2021 or 25-02-2021 or if today is 08-03-2021 or today is 20-03-2021, then the range should be ???

2. If today is 21-03-2021 or 31-03-2021 or if today is 07-04-2021 or today is 20-04-2021, then the range should be ???

[VIRAJ_MISTRY]

Hi,

The month range is constant 21st this month to 20th next month.
Any date that falls with this range, only that should be visible.

General month cycle is from 1 to end of month. My month cycle will start on 21st, time 00:00AM of every month till 20th, time 11:59PM of next month.

Since today is 04-04-2021, then I should be able to see anything from 21-03-2021 onwards till 20-04-2021, even if there is on data for future dates, it will be as days go by daily.

1. 21-02-2021 - range will be 21-02-2021 to 20-03-2021
   25-02-2021 - range will be 21-02-2021 to 20-03-2021
   08-03-2021 - range will be 21-02-2021 to 20-03-2021
   20-03-2021 - range will be 21-02-2021 to 20-03-2021

2. 21-03-2021 - range will be 21-03-2021 to 20-04-2021
   31-03-2021 - range will be 21-03-2021 to 20-04-2021
   20-04-2021 - range will be 21-03-2021 to 20-04-2021

and so on.

The range will change if today is 21st and time is 00:00 AM

I have tried one of your expression

OR(
AND(DAY([Date])>=21, MONTH([Date])=MONTH(TODAY())),
AND(DAY([Date])<21, MONTH([Date])=MONTH(EOMONTH(TODAY(),1)))
)

but this doesn’t work for me.

[Suvrutt_Gurjar]

Please try

IFS(

DAY(TODAY())>=21,
AND([Date] >=DATE(TEXT(TODAY(), “MM-21-YYYY”)), [Date]<= (EOMONTH(TODAY(),0)+20)) ,

DAY(TODAY())<=20,
AND([Date]<=DATE(TEXT(TODAY(), “MM-20-YYYY”)), [Date]>= (EOMONTH(TODAY(),-2)+21))
)

Please test well for edge cases such as year rollover from December to January etc.

[VIRAJ_MISTRY]

HI @Suvrutt_Gurjar ,

Thank You.

Your filter condition is working right now. I will check for year end.

[Suvrutt_Gurjar]

Thanks for the update, the expression could be further optimized as below. Since there are only 2 conditions in IFS(), an IF() could be used instead.

IF(

DAY(TODAY())>=21,

AND([Date] >=DATE(TEXT(TODAY(), “MM-21-YYYY”)), [Date]<= (EOMONTH(TODAY(),0)+20)) ,

AND([Date]<=DATE(TEXT(TODAY(), “MM-20-YYYY”)), [Date]>= (EOMONTH(TODAY(),-2)+21))
)