Hello,
Are you putting the expression into the advanced filter editor, or the calculated item editor?
The expression for a filter needs to return a boolean (true/false) so it would need to be something like:
TreatAs(_Number_, month('Period'n)) - rolling_month > 0 (or whatever would actually make sense)
There needs to be a comparison operator like >, <, =, or In.
The Month() operator requires a date value, so your Period parameter would need to be a date parameter. If you apply a month format to the date, it would still display values like 'January'.
In order to subtract days, your Period also will need to be a date. The date value could have a month/year format with values like JAN2023, if that helps.
All in all, this is not an easy thing to do in VA.
In the Advanced Filter window, under Conditions, you will find these:
If you select 'Last 30 days' it will generate syntax like this:
'Date'n >= TreatAs(_Date_, ( TreatAs(_Number_, DatePart(Now())) - 30 ))
You can use this as a starting point to build an expression. You can substitute your Period parameter for the "DatePart(Now())"
'Date'n >= TreatAs(_Date_, ( TreatAs(_Number_, 'Period'p) - 30 ))
Building on that, you could code some IF/ELSE logic to handle the rolling_month parameter:
IF 'rolling_month'p = 1
RETURN 'Date'n >= TreatAs(_Date_, ( TreatAs(_Number_, 'Period'p) - 30 ))
ELSE IF 'rolling_month'p = 3
RETURN 'Date'n >= TreatAs(_Date_, ( TreatAs(_Number_, 'Period'p) - 90 ))
{and so on}
This should work, it will just take a little time and trial and error to get it working the way that you want.
Sam
... View more