Slice with last X amount of rows of a table

[cboteros]

Hello everyone,

I hope you can guide me. I have a table and I want to create a slice which will return only the last X amount of rows (for example, last 6 rows, which will represent the last 6 months in my case).

How can I achieve this? I guess that it will be with the “Row filter condition” in the slice, but I can’t see how to achieve this.

[Suvrutt_Gurjar]

Could you add following?

Is the X number of months a fixed value or user selectable?
What is the month identifying column in each row?

[cboteros]

Hello @Suvrutt_Gurjar ,

I have a table called “EXPENSES” and a row [Month] which is of “Date” type which is always the 1st day of the month of the date (so, 01/01/2021 for Jan-2021, 01/02/2021 for Feb-2021, and so on).

I have another column [MonthExpenses] which is of type “Decimal”.

Right now lets consider “X” as a fixed number, eventually I might see if I can convert it into a User setting.

My ultimate intention with this slice is to create a Col Series chart of the expenses in the last X months.

[Suvrutt_Gurjar]

Please try following for the slice filter expression

IN([Key Column Name], TOP(ORDERBY(EXPENSES[Key Column Name], [Month], TRUE), 6))

help.appsheet.com

ORDERBY()

Sort row references

help.appsheet.com

TOP()

Initial list items

[cboteros · Accepted solution]

Thank you @Suvrutt_Gurjar , that is an interesting solution!

Nevertheless, I just saw that Appsheet was asking to type a description of the filter condition I wanted to, and so I typed “[Month] is in the last 6 months” and appsheet came with this formula which also works very nice!

AND(
[Month] >= EOMONTH(TODAY(), -1 - 6) + DAY(TODAY()),
[Month] <= TODAY()
)

NOTE FOR OTHER READERS: This works nicely in my case because how my data is populated in my table (1 month per row, not repeated).

[Suvrutt_Gurjar]

Wow !That is neat suggestion by AppSheet. Appsheet suggested expression should be more economical from sync time point of view.

[cboteros]

@Suvrutt_Gurjar I have now another question.

I tried to replace the 6 (aka, my “X” value) for a new User Setting, but I’m getting an error.

What I did is that I replaced the “6” for:

USERSETTINGS([settingsLastXMonths])

So, my end formula looks like this:

AND(
[Month] >= EOMONTH(TODAY(), -1 - USERSETTINGS([settingsLastXMonths])) + DAY(TODAY()),
[Month] <= TODAY()
)

But AppSheet is showing me this error:

Function ‘USERSETTINGS’ should have exactly 1 parameters, the name of an option from the user settings

My User Setting “settingsLastXMonths” is an Enum of type Number with the options 6, 12, 18, and 24

Would you know what I’m doing wrong?

[Suvrutt_Gurjar]

Usersettings syntax is different than that for columns, please try with

USERSETTINGS(settingsLastXMonths) instead of USERSETTINGS([settingsLastXMonths])

[cboteros]

Thank you very much! That did the trick it’s looking nice!