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.
Solved! Go to 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).
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?
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.
Please try following for the slice filter expression
IN([Key Column Name], TOP(ORDERBY(EXPENSES[Key Column Name], [Month], TRUE), 6))
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).
Wow !That is neat suggestion by AppSheet. Appsheet suggested expression should be more economical from sync time point of view.
@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?
Usersettings syntax is different than that for columns, please try with
USERSETTINGS(settingsLastXMonths) instead of USERSETTINGS([settingsLastXMonths])
Thank you very much! That did the trick itโs looking nice!
User | Count |
---|---|
36 | |
33 | |
27 | |
23 | |
18 |