Hello All, first of all I would like to apologize if this already exists I have tried to search for it but had no success.
I am trying to create a filter which will allow an automated financial report to show the following logic
If current month is equal to January
Then show entire previous year
Else show previous month
Each portion of the following expression runs when used separately as a standalone filter, however when I add them together in an IF statement I receive the below error message. Any suggestions would be highly appreciated!
if ([Statistic Period (Month)] = '1')
then ([Statistic Period (Year)] = (year(getdate())-1))
else (((Year([Operational Reporting].[Jobfile].[Statistic Period]) = Year(getdate())) AND (MONTH([Operational Reporting].[Jobfile].[Statistic Period]) = MONTH(getdate())-1)))
Quote from: helpme1977 on 19 Dec 2018 08:47:52 AM
Hello All, first of all I would like to apologize if this already exists I have tried to search for it but had no success.
I am trying to create a filter which will allow an automated financial report to show the following logic
If current month is equal to January
Then show entire previous year
Else show previous month
Each portion of the following expression runs when used separately as a standalone filter, however when I add them together in an IF statement I receive the below error message. Any suggestions would be highly appreciated!
if ([Statistic Period (Month)] = '1')
then ([Statistic Period (Year)] = (year(getdate())-1))
else (((Year([Operational Reporting].[Jobfile].[Statistic Period]) = Year(getdate())) AND (MONTH([Operational Reporting].[Jobfile].[Statistic Period]) = MONTH(getdate())-1)))
Hi,
Using if-then-else constructs in a filter expression generally isn't a good idea. A filter needs to evaluate to a TRUE or a FALSE outcome, so using a Boolean-type expression is usually the way to go.
I would probably use the following expression:
(extract( month, current_date ) = 1 and [Statistic Period (Year)] = extract( year, _add_years(current_date, -1)))
or
(extract( month, current_date ) <> 1 and [Statistic Period (Year)] = extract( year, current_date) and [Statistic Period (Month)] = extract( month, _add_months( current_date, -1)))
Cheers!
MF.
Quote from: MFGF on 19 Dec 2018 09:05:00 AM
Hi,
Using if-then-else constructs in a filter expression generally isn't a good idea. A filter needs to evaluate to a TRUE or a FALSE outcome, so using a Boolean-type expression is usually the way to go.
I would probably use the following expression:
(extract( month, current_date ) = 1 and [Statistic Period (Year)] = extract( year, _add_years(current_date, -1)))
or
(extract( month, current_date ) <> 1 and [Statistic Period (Year)] = extract( year, current_date) and [Statistic Period (Month)] = extract( month, _add_months( current_date, -1)))
Cheers!
MF.
Brilliant, thank you so much!!