I have list report with columns Customer Id,Customer_Name,Revenue July 10/June 11,Revenue July 11/June 12, Revenue Last 3 Months, Revenue Last 12 Months.
For Revenue last 3 months am using as
total(if([Fiscal_Year_Month] between current_month and _add_months(current_month,-3) then
[Revenue] else (0))
For this am getting an error as Logic error.
If I run the report in June 12 then I want to get Mar 12,Apr 12,May 12 total Revenue.
How can I get last 3 months Revenue for customer.Please let me know how can I achieve this.
Thanks,
What does "getting an error as Logic error" mean? Is there a particular error message or is the result of the expression incorrect?
The "current_month" in your expression is not familiar to me. Did you mean current_date or is this some other function you have in your database? The _add_months function expects a date datatype so if current_month isn't returning a date then I don't think that part of the expression would be correct.
If I read your question correctly: You already calculated the Fiscal_Year_Month. Is this query item a number or a date. If it's a number then the between won't work because the between filter is with dates. Then you have to convert [Fiscal_Year_Month] to a Date or your between statement to Numbers
I posted lot of relative time categories on this forum some time back. For Last 3 Months you can do this -
Create a Data Item with this expression :
[Date] between
_first_of_month(_add_months(current_date,-3)) and
_last_of_month(_add_months(current_date,-1))
In your column that will show the values for last 3 Months use this -
If
([Data Item])
then ([Revenue])
else (0)
[Date] refers to the Date column in your model which points to [Fiscal_Year_Month]. If your [Fiscal_year_Month] is giving you integer values of month, then it's even easier. You can use extract function
to determine the current month number and then compare that against your column.
[Fiscal_year_Month] >= (extract(month,current_date) - 3) and
[Fiscal_year_Month] <= (extract(month,current_date) - 1)
Use the above in your Data Item.