12 rolling month

[#1]

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

[#2]

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

[#3]

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

[#4]

And if you put the Month(Date) as a Dimension ?

It should show you per month , the amount of the last 12 months no ?

[#5]

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

[#6]

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 .

[#7]

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

[#8]

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

[#9]

Thanks ,

Your answer is helpful but not exactly I'm looking for.

Cheers,

Nima

[flipside]

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