Filter report to pull prior month when its from a previous year

[gats1527]

We are trying to create a report that will, when scheduled, pull the prior months expense data but for the first of the year, i need to have the report pull December 2020 data.  I can manually set the filter to pull the year 2020 and the month 12, but when scheduled, i need the filter to execute automatically.

Any thoughts on how to easily accomplish this?

Thank you

Tom

[MFGF]

We are trying to create a report that will, when scheduled, pull the prior months expense data but for the first of the year, i need to have the report pull December 2020 data.  I can manually set the filter to pull the year 2020 and the month 12, but when scheduled, i need the filter to execute automatically.

Any thoughts on how to easily accomplish this?

Thank you

Tom

Hi,

Are you using a relational package or a dimensional package? The solution is different for each.

If it's relational, then something like:

(extract(month, current_date) = 1 AND [Your Year item] = extract(year, current_date)-1 AND [Your Month item] = 12) OR (extract(month, current_date) > 1 AND [Your Year item] = extract(year, current_date) AND [Your Month item] = extract(month, current_date)-1)

If it's dimensional, then use a dimensional expression in your report that uses a lag() function based on the current month member of the month level.

Cheers!

MF.

[gats1527]

thank you.  this is exactly what i needed the output to be

[BigChris]

You might be able to do something simpler than this:

(extract(month, current_date) = 1 AND [Your Year item] = extract(year, current_date)-1 AND [Your Month item] = 12) OR (extract(month, current_date) > 1 AND [Your Year item] = extract(year, current_date) AND [Your Month item] = extract(month, current_date)-1)

Code Select Expand

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

It all depends on your data really and the structures you're using...many ways to skin a cat and all that  :D