Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello All,
I have a problem with my expression, I have a field in my table name DATE which is like
2011-12-01
2011-11-01
2011-10-01
..
..
..
. I want to filter the report for last 12 month or better to say I need the 12 rolling month in the report.
Any idea is appreciated.
Thanks
Nima
This Year Rolling Sales Amount : sum( {$< NumOrderDate = { '>= $(LastYearvarDate) <= $(varDate)'}>} NetSalesAmount )
Tha variables are
varDate = variable choosen in a calendar Objet
Last YearvarDate = num(AddMonths(date($(varDate)) , -12 ))
Hope that helps!
Philippe
Thanks Philippe,
But I need to show the last 12 months in the report that I have.
I don't think if it works for me.
Thanks,
Nima
And if you put the Month(Date) as a Dimension ?
It should show you per month , the amount of the last 12 months no ?
Ok ,
This the case, maybe I did not explain it very well.
What I want is when I open the report the report shows me the last 12 month of the DATE that I have.
I put this expression in a trigger but it does not work:
=('>' & date(addmonths(Today(),-12), 'YYYY-MM-01') AND
'<' & date(Today(), 'YYYY-MM-01') )
Thanks
what kind of object are you using ?
A chart ? A listBox ? something Else
Try
if (DATE <= Today(0) and DATE >=Addmonths(Today(0) , -12 ) , DATE , null())
Then Remove the null .
Thanks,
The thing is I need it on the trigger not in the object because I don't want to lose the rest of my DATE .
When it's on the trigger , when I open the report it shows just the 12 month then I have this option to clear the selection and select other month in the month table.
Thanks
Hi Nima,
For my rolling 12 month report used the following formula under an expression
the report is for the last completed 12 months sales for the company's branches
=if(InvoiceDateID <= MonthEnd(Today(),-1) and InvoiceDateID > MonthEnd(Today(),-13) , BRANCHES,Null())
so if i were to do it for the last rolling 365 days I use
=if(InvoiceDateID <= Today()-1 and InvoiceDateID >= Today()-366 , BRANCHES ,Null())
hope this helps
Tara
Thanks ,
Your answer is helpful but not exactly I'm looking for.
Cheers,
Nima
Whenever I am faced with rolling date periods, I usually create a new field within the load script to hold a flag value.
For example, if Date within last 12months, set YTDflag to 1, else set YTDflag to 0. It makes expressions a lot easier to manage, in your case you just need to make the selection of YTDflag = 1.
Hope this helps
flipside