Need help with Rolling 12 months and and prior year rolling 12 months filter

[danii64]

HI all, Happy Monday.

I have requirement where i have to show the sales data for the rolling 12 months and prior year rolling 12 months. I have Sys_date data item and sales data item. I have extracted months and Year from the sys_date.
I have months in column and Year in row with the sales as the measure in crosstab.

This is what i have as a filter right now. It shows me data from april 2014 to aug 2015. Jan Feb and March of 2014 is coming blank.
If i define the manual prompt value for user to select it shows data for all 12 months of 2014 and upto aug 2015 as thats as recent as it can report for.

below is the filter i have currently defined...


[sys_date] between
_add_days(_add_days(current_date,-1),((_day_of_year(_add_days(current_date,-1))-1)*-2))
  and
(_last_of_month (_add_months(current_date,-1)))


thanks in advance!!

[colt]

Not sure what you want.
You have Years in Rows and Months in Columns.
Which months should be shown on row actual year (2015)
and which should be shown on row 2014 ?

The behaviour of your filter is quite clear. You use function _day_of_year and substract this twice from current date. That means when your in September you go back a certain number of days up to 01.01. of year and then again go back the same number of days so you will be somewhere in April and data from Jan-Mar 2014 will not be shown.

Think if you can use a or Statement in your filter and _add_year.