Is there a way to create a filter on GI to show the last seven days, last month, and to forecast next month? I know there is the @month and the @today parameter but couldn’t find a way to add days to it. I tried @today-7 and it does not filter the last seven days.
Solved
Date Filter in a Generic Inquiry
Best answer by davidnavasardyan
Hello
In Acumatica, creating a filter to show data for the last seven days, last month, or to forecast next month can be done by utilizing relative date keywords in combination with the appropriate operator in the filter condition.
However, to create more complex relative date filters (like "the last seven days"), you'll need to set up a pair of conditions to form a date range.
Here's how you can do it:
-
Last seven days: To get the data for the last seven days, create two conditions:
- Condition 1:
[YourDateField],Greater Or Equal,@Today-7. - Condition 2:
[YourDateField],Less Than,@Today.
- Condition 1:
-
Last Month: If you want to get the data for the previous month, use:
- Condition 1:
[YourDateField],Greater Or Equal,@Month-1. - Condition 2:
[YourDateField],Less Than,@Month.
- Condition 1:
-
Next Month Forecast: If you want to forecast the next month, use:
- Condition 1:
[YourDateField],Greater Or Equal,@Month+1. - Condition 2:
[YourDateField],Less Than,@Month+2.
- Condition 1:
The @Today-7 and @Month-1 syntax you mentioned works as expected in my experience. If this isn't the case, please verify that you have the latest updates or patches for your version of Acumatica.
Please note that these filters will work dynamically. Every time you run the GI, it will calculate the dates based on the current date (for @Today) or the current month (for @Month).
+1You can use the DateAdd formula with a negative interval, e.g. =DateAdd(Today(),’d’,-7) for a week ago or =DateAdd(Today(),’m’,-1) for a month ago.
+2Hello
In Acumatica, creating a filter to show data for the last seven days, last month, or to forecast next month can be done by utilizing relative date keywords in combination with the appropriate operator in the filter condition.
However, to create more complex relative date filters (like "the last seven days"), you'll need to set up a pair of conditions to form a date range.
Here's how you can do it:
-
Last seven days: To get the data for the last seven days, create two conditions:
- Condition 1:
[YourDateField],Greater Or Equal,@Today-7. - Condition 2:
[YourDateField],Less Than,@Today.
- Condition 1:
-
Last Month: If you want to get the data for the previous month, use:
- Condition 1:
[YourDateField],Greater Or Equal,@Month-1. - Condition 2:
[YourDateField],Less Than,@Month.
- Condition 1:
-
Next Month Forecast: If you want to forecast the next month, use:
- Condition 1:
[YourDateField],Greater Or Equal,@Month+1. - Condition 2:
[YourDateField],Less Than,@Month+2.
- Condition 1:
The @Today-7 and @Month-1 syntax you mentioned works as expected in my experience. If this isn't the case, please verify that you have the latest updates or patches for your version of Acumatica.
Please note that these filters will work dynamically. Every time you run the GI, it will calculate the dates based on the current date (for @Today) or the current month (for @Month).
Software Engineer | Acumatica ERP Developer at ACUPOWER LTD
These filter conditions are not working for widget filter settings
Hope I am doing it correctly, if not please correct me. Thanks.
Below is the error screenshot…
Reply
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.