Thank you ZZ. Your solution worked. However, I have run into another hitch. In the view in the pic below, I am able to create the rolling months. However, I would like to be able to limit this view to 12 months at a time while making sure all the numbers in my view take into the account months that are not in the view when rolling up. So for example in the view below, I would like for January 2016 to show me a rolling of the last 12 months even though those months are not in the view. My desired outcome is for my user to pick a June 2016 for example and the view shows June 2016 + the preceding 11months. I tried creating a rolling 12 Parameter but I am finding that It limits the numbers in the view to just occurrences in that month.
1 of 1 people found this helpful
If you use parameter as a month pickup ( I assume the data type of your parameter is date), then you could create a filter like
lookup(min(datetrunc('month',[your date field])),0) >= dateadd('month',-11,datetrunc('month',[your parameter]))
and then drag it to filter and select true. hope this makes sense.
Hi ZZ. Thank you. The calculation works in that it rolls up the months correctly. However, it does not limit the cross tab view to 12 months which is also part of what I am looking for. Any ideas?
ZZ so I modified your calculation some more to look like this:
DATEDIFF('month',LOOKUP(MIn(DATETRUNC('month', [Reportdate])),0),LOOKUP(min(DATETRUNC('month',[Select Month])),0))<=11
and DATEDIFF('month',LOOKUP(MIn(DATETRUNC('month', [Reportdate])),0),LOOKUP(min(DATETRUNC('month',[Select Month])),0))>=0
This is what I get when I drop it in my view when what I am really trying to achieve is the second picture below.
This is my desired view...somewhat. Only diff is I need to show only 12 months in my view at any given time with my parameter. Filtering manually on my reportdate dimension throws the numbers off.