List View Report LPL Filter on Posting Date

Does anyone know how to add a filter to a List View Report that limits a date field dynamically? For example, I want to deliver a csv report of financial transaction details to a group of people on an automated schedule. However, I only want to send them transactions that posted in the previous month. I have the field Posting Date to play with, so I'm looking for something like Posting Date >= today minus 1 month. Thank you.

Find more posts tagged with

Accepted answers

unknown

You don't need to enter the full syntax in the definition section. The field Name and Type are declared in the header above. All you need to enter in the definition section is:

return (current date - 1 month)

Thank you for the syntax Dr. Awesome. Nice approach that also solves for the year as well.

All comments

andy

You might try using "current date", e.g. PostingDate >= current date - 30, though the number of days per month varies so that's not a great solution.

There are also type operators that can grab date components that you might explore, but I don't think you can apply them to "current date".

unknown

You can try to create a derived field and then do an instance selection on the list

Field

ThisMonth = current date - 1 month

List

Instance Selection
where (This Month)

Dr. Awesome

Do you want today minus 28-31 days (rolling last month)? Or do you want today, go back to last month and get all of the things posted last month?

Say "today" is July 13th. You want all of June? or June 13 - July 13?

The following operators are relatively new and likely very useful here.

days in month // “ – number of days in the date’s month (28-31)

days in year // “ – number of days in the date’s year (either 365 or 366)

(first | last) date in year // valid with Year, Period, Date, and TimeStamp

(first | last) date in month // valid with Period, Date, and TimeStamp

(first | last) date in week // valid with Date and TimeStamp

Stace Webley

Yes, that would be great to look back at all postings for the prior month regardless of what day it is. Since I am looking at financial transaction detail isn't there a field for period? Maybe current period minus 1?

unknown

I believe what you are trying to do is match the month of the PostingDate to the prior month and the year of the PostingDate to the current year. In order to do this for GLTransactionDetail I would do the following. The first step is necessary because the "current date" value is an array and will not return the "month" numerically so you must first set-up a derived date field to hold the "Current Date". There may be a more direct way to achieve this but I have tested this method and will produce the instance selection you are looking for.

1) Create a Derived field that is a Date Type field with the syntax:

DerivedCurrentDate is a DerivedField

type is Date

return current date

2) Create a Derived field to retrieve the numeric month from the DerivedCurrentDate field:

DerivedCurrentMonth is a DerivedField

type is Numeric size 2

return ZCurrentDate month

3) Add this instance selection to your GLTransactionDetail list and will always show the transactions with posting dates from the prior period:

Instance Selection

where (FinanceEnterpriseGroup = actor.context.FinanceEnterpriseGroup

and PostingDate year = current year

and PostingDate month = (DerivedCurrentMonth - 1))

unknown

If you are looking the Finance Transaction Detail (List View),you can filter and select Posting Date and use "In Range" and select the date range you range to see and then can export.

Stace Webley

Hi Linda - Thank you for the suggestion. We would do that if we were running the report manually. However, I'm building a report that will run on a schedule and send data to a distribution group containing last months postings for a certain GL string.

Jim Norbut

Dr. Awesome said : "DerivedField needs to be created in Config Console on the BusinessClass the List is centered on.

We are discussing allowing this to be done inline in the report down the road but that will take a while to get to you."

(did this ever get accomplished.....i just had a use case for this exact thing...and ended up using the derived field since i didn't know if it would be possible via an inline filter or whatever Dr. Awesome was mentioning)

Dr. Awesome

Yes, what Dr. Awesome says Dr. Awesome does!

So yeah, coming in the 2025.04 release we have support to do this type of inline date and period computation so you can avoid adding DerivedFields. As well as some other nice to haves to help with filtering and computing.

Definition:

Display:

Also, we did some work in the Filtering side of personalization:

On an Audit List, Numeric type field, we automate difference and percent difference for you

% diff

diff

And in Condition builder we added a within option:

I'll see if I can find create some more examples of date and period math for inline computes for doco.

robert-canham

One of my users today asked about if/else statements in a compute. Is that coming? They were trying to give a text description to a boolean field in the report.

abigail-mayes

I wonder this too. Is there the ability to do if/else statements using compute?

Copyright © 2025 Infor. All rights reserved.