Filter Query: Between Prev Month and Current Date

[t0mato]

Hello!

How would I write an expression to capture between the previous month and current date?

I know previous month would follow this:

Code Select Expand

[Date] between
_first_of_month (
_add_months (current_date,-1)
)
and
_last_of_month(
_add_months(current_date,-1)
)


But how can I add an additional AND to the statement so that it captures between all of the previous month, AND the current date?

I need to run a report on every first Friday of the month capturing all data from the previous month, to the Friday in which it is run.

Thanks!

[MFGF]

Hello!

How would I write an expression to capture between the previous month and current date?

I know previous month would follow this:

Code Select Expand

[Date] between
_first_of_month (
_add_months (current_date,-1)
)
and
_last_of_month(
_add_months(current_date,-1)
)


But how can I add an additional AND to the statement so that it captures between all of the previous month, AND the current date?

I need to run a report on every first Friday of the month capturing all data from the previous month, to the Friday in which it is run.

Thanks!

Hi,

If I'm understanding what you're looking for, it's all data from the beginning of the previous month up to and including the current date?

Wouldn't it just be:

[Date] between
      _first_of_month (
      _add_months (current_date,-1)
      )
      and
      current_date

Or am I missing something obvious (which is normal for me) :-)

Cheers!

MF.

[t0mato]

Hi MFGF, thanks for the response.

I believe you are understanding correctly and that should work. My understanding is that for a previous month query, you would need to write it as I had mentioned in the first post, utilizing BETWEEN just to capture the previous month.

I had completely forgotten that _add_months(current_date, -1) will still capture all data from the previous month as well.

This should solve it. I think I tried what you had proposed and received an error - I likely mistyped it.

Thanks!

[MFGF]

Hi MFGF, thanks for the response.

I believe you are understanding correctly and that should work. My understanding is that for a previous month query, you would need to write it as I had mentioned in the first post, utilizing BETWEEN just to capture the previous month.

I had completely forgotten that _add_months(current_date, -1) will still capture all data from the previous month as well.

This should solve it. I think I tried what you had proposed and received an error - I likely mistyped it.

Thanks!

Hi,

To break it down (and showing dates in a yyyy-mm-dd format):

current_date would resolve to 2022-02-02
_add_months(current_date, -1) would resolve to 2022-01-02 (ie a month before the current date)
_first_of_month(_add_months(current_date, -1)) would resolve to 2022-01-01 (ie the first day of the month that came before the current date's month)

so a filter with the expression
[Date] between _first_of_month(_add_months(current_date, -1)) and current_date

would resolve to

[Date] between 2022-01-01 and 2022-02-02

A date filter using a between operator needs arguments that resolve to the earliest date and the latest date in the range you require.

Cheers!

MF.

[t0mato]

Thank you for the information!

Last question on this, how about if I want to exclude YEAR from the equation/ how would I adjust what's been provided?

I also need to do a seniority based capture that captures, essentially, everyone with the [Seniority Date] between the range you had provided (previous month to current date), but EXCLUDE the year from this - just need month.

I tried creating a separate data item that extracts the YEAR from [Seniority Date] and use that as a separate filter in which it is <> year(current_date) and used alongside your given expression but this returns no data.

Ultimately, how would the expression look if I need all data from the previous month (excluding year) i.e. January and current month, i.e. February - it does not need to be to the current date (e.g. February 2), just month (February).

I tried throwing some things in MONTH to tell it just capture at the month level, but something is wrong here:

Code Select Expand

[Seniority] between
      _first_of_month (
      _add_months (month(current_date,-1))
      )
      and
      month(current_date))

[MFGF]

Thank you for the information!

Last question on this, how about if I want to exclude YEAR from the equation/ how would I adjust what's been provided?

I also need to do a seniority based capture that captures, essentially, everyone with the [Seniority Date] between the range you had provided (previous month to current date), but EXCLUDE the year from this - just need month.

I tried creating a separate data item that extracts the YEAR from [Seniority Date] and use that as a separate filter in which it is <> year(current_date) and used alongside your given expression but this returns no data.

Ultimately, how would the expression look if I need all data from the previous month (excluding year) i.e. January and current month, i.e. February - it does not need to be to the current date (e.g. February 2), just month (February).

I tried throwing some things in MONTH to tell it just capture at the month level, but something is wrong here:

Code Select Expand

[Seniority] between
      _first_of_month (
      _add_months (month(current_date,-1))
      )
      and
      month(current_date))

Hi,

Sorry, I'm not quite understanding what your requirement is here. Can you describe the result you are looking for? For example, when you say you want to exclude year from the equation, do you mean you want results from the beginning of the year to the current date? Or do you want results for the same time period every year? Or do you want results from other years (excluding this year) for the same time period? Or something else?

Cheers!

MF.

[t0mato]

Hi,

Sorry, I'm not quite understanding what your requirement is here. Can you describe the result you are looking for? For example, when you say you want to exclude year from the equation, do you mean you want results from the beginning of the year to the current date? Or do you want results for the same time period every year? Or do you want results from other years (excluding this year) for the same time period? Or something else?

Cheers!

MF.

Thanks again for the help. Maybe explaining it this way will clear it up:

I have an Anniversary report with [Seniority Date] included. The report lists all active associates. This data is used to highlight associates with [Seniority Date] within the current month, and the month prior.

So if we're in February, this report would be manually manipulated in Excel to extract [Month] and [Year] from [Seniority Date], then filtered to [Month] = 1 (previous month) and 2 (current month). The only other caveat here is that we do not want the [Year] to be the current year, so the [Year] field is filtered to anything except the current year. [Seniority Date], in our system, is essentially the date someone was hired so we don't want the current year as that isn't a list of anniversaries, rather a list of new hires in the previous and current month.

Ultimately, I need an expression to somehow filter [Seniority Date] to all of the previous month and current month, but NOT current year.

[MFGF]

Thanks again for the help. Maybe explaining it this way will clear it up:

I have an Anniversary report with [Seniority Date] included. The report lists all active associates. This data is used to highlight associates with [Seniority Date] within the current month, and the month prior.

So if we're in February, this report would be manually manipulated in Excel to extract [Month] and [Year] from [Seniority Date], then filtered to [Month] = 1 (previous month) and 2 (current month). The only other caveat here is that we do not want the [Year] to be the current year, so the [Year] field is filtered to anything except the current year. [Seniority Date], in our system, is essentially the date someone was hired so we don't want the current year as that isn't a list of anniversaries, rather a list of new hires in the previous and current month.

Ultimately, I need an expression to somehow filter [Seniority Date] to all of the previous month and current month, but NOT current year.

So you're looking for the month part of [Seniority Date] to be the same month as the current month or the prior month, but not in the current year? (eg if you were to run the report now, it would return results with January or February in the month but not 2022 in the year?)

I think this might work:

(extract(month, [Seniority Date]) = extract(month, current_date) OR extract(month, [Seniority Date]) = extract(month, _add_months(current_date, -1))) AND extract(year, [Seniority Date]) < extract(year, current_date)

Cheers!

MF.

[t0mato]

So you're looking for the month part of [Seniority Date] to be the same month as the current month or the prior month, but not in the current year? (eg if you were to run the report now, it would return results with January or February in the month but not 2022 in the year?)

I think this might work:

(extract(month, [Seniority Date]) = extract(month, current_date) OR extract(month, [Seniority Date]) = extract(month, _add_months(current_date, -1))) AND extract(year, [Seniority Date]) < extract(year, current_date)

Cheers!

MF.

This logic makes sense and works perfectly. The OR is what I was missing from my train of thought. Thanks so much!

[MFGF]

This logic makes sense and works perfectly. The OR is what I was missing from my train of thought. Thanks so much!

That's great news!

You could also try:

extract(month, [Seniority Date]) in (extract(month, current_date), extract(month, _add_months(current_date, -1))) AND extract(year, [Seniority Date]) < extract(year, current_date)

It's a little neater :-)

Cheers!

MF.

[t0mato]

That's great news!

You could also try:

extract(month, [Seniority Date]) in (extract(month, current_date), extract(month, _add_months(current_date, -1))) AND extract(year, [Seniority Date]) < extract(year, current_date)

It's a little neater :-)

Cheers!

MF.

I apologize for keep coming back but the query keeps needing to change. I think this is a small adjustment.

Right now, the report is capturing at the month level for the previous month (which is perfect) and year < current, but for up until the current date portion of the expression, we need just it to stop at the current date, not current month.

In sum: capture between previous month and year < current year, to current date and year < current year. Right now, this second part is capturing to current month, we just need it to be to current date

So we need this between any dates in the previous month to current date where [Seniority Date] < year(current_date) for both portions of the expression.

My thoughts are as follows:

Code Select Expand

[Seniority Date] between _first_of_month(current_date) and current_date */to capture current month to date /* to capture current month to date*/

OR extract(month, [Seniority]) = extract(month, _add_months(current_date, -1))) AND extract(year, [Seniority]) < extract(year, current_date) /* to capture the previous month. I believe the AND applies to both statements in the expression, which is what it should do*/

[MFGF]

I apologize for keep coming back but the query keeps needing to change. I think this is a small adjustment.

Right now, the report is capturing at the month level for the previous month (which is perfect) and year < current, but for up until the current date portion of the expression, we need just it to stop at the current date, not current month.

In sum: capture between previous month and year < current year, to current date and year < current year. Right now, this second part is capturing to current month, we just need it to be to current date

So we need this between any dates in the previous month to current date where [Seniority Date] < year(current_date) for both portions of the expression.

My thoughts are as follows:

Code Select Expand

[Seniority Date] between _first_of_month(current_date) and current_date */to capture current month to date /* to capture current month to date*/

OR extract(month, [Seniority]) = extract(month, _add_months(current_date, -1))) AND extract(year, [Seniority]) < extract(year, current_date) /* to capture the previous month. I believe the AND applies to both statements in the expression, which is what it should do*/

So what you're saying is that right now the expression gives you results including all days in February for previous years, but you want it to give you results for only the days from the beginning of February to the current day for previous years?

((extract(month, [Seniority Date]) = extract(month, current_date) AND extract(day, [Seniority Date]) <= extract(day, current_date)) OR extract(month, [Seniority Date]) = extract(month, _add_months(current_date, -1))) AND extract(year, [Seniority Date]) < extract(year, current_date)

Does the above expression give you what you need?

Cheers!

MF.

[t0mato]

So what you're saying is that right now the expression gives you results including all days in February for previous years, but you want it to give you results for only the days from the beginning of February to the current day for previous years?

((extract(month, [Seniority Date]) = extract(month, current_date) AND extract(day, [Seniority Date]) <= extract(day, current_date)) OR extract(month, [Seniority Date]) = extract(month, _add_months(current_date, -1))) AND extract(year, [Seniority Date]) < extract(year, current_date)

Does the above expression give you what you need?

Cheers!

MF.

This works perfectly and is exactly what was needed. You're a life saver, thanks.