Re: How to truly get "Last Month"

Greg_L · 01-04-2020 07:30 AM

In the article for the MONTH() expression, it states you can use MONTH(TODAY())-1 to get the integer value for last month. So you could also use MONTH(TODAY())-2 to get the month integer from 2 months ago, etc…

Problem is, that isn’t true. If it’s January (month 1) and you use MONTH(TODAY())-1 and expect to get December (12), you’d be wrong. The article for the MONTH() expression is misleading on how to get last month, or 2 months ago, etc…

So, to truly get Last Month, you would use ABS(12+MONTH(TODAY())-1) and you can change that “-1” to go back more months.

This way if it’s currently January, the formula will result in 12, for December, as expected.

**EDIT: USE THE CORRECT EXPRESSION BELOW PROVIDED BY STEVE

Steve

You are correct that the help doc doesn’t take into account the turn of the year, but your expression has its own limitations. If today is in February, ABS(12+MONTH(TODAY())-1) = ABS(12+2-1) = ABS(13) = 13. If today is in December, ABS(12+MONTH(TODAY())-1) = ABS(12+12-1) = ABS(23) = 23. So your expression is only correct for January.

Better:

MONTH(EOMONTH(TODAY(), -1))

Or:

(MOD((MONTH(TODAY()) - 1 + 12 - 1), 12) + 1)

(-1 for 0-11 instead of 1-12, +12 to allow for Jan to Dec, -1 for last month, +1 back to 1-12 from 0-11)

Which simplifies to:

(MOD((MONTH(TODAY()) + 10), 12) + 1)

Greg_L

Thank you Steve! You’re absolutely right. I was too hasty in writing my expression specific to January.

Feets

This still isnt right when you compare to the original statement “How to truly get Last Month”. I’m using your revised formula to evaluate a field, “Referral Date”. Objective: report all the referrals we received last month. Using your formula(s) results in any given February. It doesnt give February 2021. I get ALL February’s from ALL years. How do I resolve for, literally, last month?

Thanks!
Mark

Steve

This?

TEXT(EOMONTH([Date], -1), "MMMM YYYY")

Appsheet_this

Hi Steve, so basically what is this formula doing? because i'm having the same issue, i get all the sales from "February" of all years, but i need the formula to work for any last year month.

EDIT: ohhh i see:

Two-argument form TEXT("2019-11-01", "dd/mm/yyyy") returns 01/11/2019

Thilina_Wick

To anyone who didn't quite understand how to implement this from @Steve 's expressions, I will drop the implementation I used to get the result I wanted.

Create a virtual column:
- With the following expression

TEXT(EOMONTH([Date],0), "MMMM YYYY")

Create a slice:
- With the following expression

[Virtual Column Name]=TEXT(EOMONTH(TODAY(), -1), "MMMM YYYY")

Profit 😎

And bam bobs your uncle, this works like a charm. Thanks @Steve !!

Steve

@Greg_L, I’ve corrected the doc. Thanks for bringing this to our attention!

retailpartnerco

So if I want to select only the entries of a table that have occured in the previous 31 days or 7 days (month or week), how would I do that?

Steve

Try:

FILTER(
  "Table",
  ([Date] >= (TODAY() - 31))
)

Or:

FILTER(
  "Table",
  ([Date] >= (TODAY() - 7))
)

retailpartnerco

Wow thanks for the quick response! I will try that.

Greg_L

@Steve I would love to see AppSheet create a DATEADD() expression that allows you to easily add (or subtract) time in different types of units (minutes, days, hours, months, etc…)

It would look like this: DATEADD([date], [#], ‘units’)

So… DATEADD(TODAY(), 10, ‘days’) returns a date that is 10 days after today.

See the way it is used over at Airtable: https://support.airtable.com/hc/en-us/articles/203255215-Formula-field-reference#date_and_time_funct...

Feets

Same result as before.

Steve

I doubt that.

How are you trying to “report all the referrals”? How are you “evaluat[ing] a field”?

Feets

I have a Yes No Field called Previous Month. I have an App Formula in that field that says
TEXT(EOMONTH([Start Date], -1), “MMMM YYYY”). I then have a slice looking for “Y” in Previous Month. the resultant view of the slice shows (of 2200 records) the subset that have a date in February in any given year. ('17-'21 inclusive).

Steve

It looks to me that you don’t really understand expressions or the Yes/No column type. I encourage you to spend some time reading the documentation available at help.appshet.com. Also, you still have not well explained your problem in a way that someone who isn’t familiar with your app can readily help you. I have no further guidance to offer at this point.

Steve

A Yes/No column expects a Yes (TRUE) or No (FALSE) value.

TEXT() does not produce a Yes/No value. Hence my conclusion you don’t understand the Yes/No column type.

That you blindly used the expression I suggested, in the wrong way, thinking it would do what you want and not understanding why it doesn’t, suggests you don’t understand the expression, and perhaps expressions more generally. Hence my suggestion you study the help documentation further.

You chose not to provide the expression for the slice row filter. I suspect you used the CONTAINS() function. If so, I would point out that CONTAINS() does not do what you need done, and would readily explain why your results are wrong. Worse yet, you might instead be using FIND().

Feets

It seems to me you don’t really understand your documentation is terrible. It seems to me that attacking someone trying to get something going and then being struck by a know it all with the answer RTFM ISNT TERRIBLY HELPFUL. It seems to me you’re a proud product dev that should stick to that and not try his clearly misguided hand at Customer Support.

Steve

Regardless, we can’t help you if you can’t communicate your problem.

Steve

Not sure this link is available to you. It might offer some context. Click through.