I would say the best practice is always to stay single table rather than splitting into multiple tables in light of consumption of those data whatever purpose afterwards. In case splitting into separate tables, you may see the difficulties to union/combine those separate ones when you need to use those.
Security filters should be the best tool to achieve this task.
This is just a sample workaround where Appsheet should provide bunch of different approaches though.
For instance, you place one physical record, such as “YYYYMM” as key to slice the table by security filter. Add expression to app formula or initial value something like text(Today(),“YYYYMM”)
By arranging this, app will automatically add yyyymm stampt to that field, which will works as reference point when, ie. which year and month the row was generated, and act as anchor to apply security filter.
Next, create the usersettings. This should be Enum type. Same name like YYYYMM. Value options you simple add the 202001, 202002, 202003… which stands for YYYYMM values. Or you create the independent table which store those yyyymm list of values and make the usersetting type to ref to refer to this table. Set the inital value to text(Today(),“YYYYMM”)
Apply the security filter to the original table with condition of [YYYYMM]=usersettings(YYYYMM)
Once the user sync the app, the table will be sliced to pass the set of row of “current month”, and the rest of rows should be ruled out.
If user like to view the old data, still they can do so by change the value of usersettings to the past month and year.
If you want to swith the change of row filter, such as “by quarter” then you simply twist this trick, using text(today(),yyyy&month(today)/3.0) something like that. The table should be refreshed by every quarter etc.
I m not testing this workaround, but should work.