So I have a chart that I need to filter based on time. I need to return past 6 months and future 6 months based on a selected month (format MMYYYY).
I have used the lastPeriod lastPeriods (6, [Selected Month]) function to return either past or future months - but am unable to figure out HOW TO filter on both past and future months.
eg month selected 032017 - chart to display months from 012017 through 062017...
Thanks, D
Based on your message, you need to create a Set of 13 month members and use that in your layout, rather than doing any filtering (As you indicated this is a Dimensional report). Your set's 13 members would consist of the following:
- 6 past months
- 1 current month
- 6 future months
Build each set individually and test as you go (seems you had some success doing this already, using the lastPeriods() function)
Then use the UNION() and Except functions to bring all 3 sets into a single set
Attached is a quick example that gets the desired outcome, as suggested above. It uses the GO Sales (Analysis) package
rocky - thanks for your response and attached example - the union spits out an error - a nasty one...I am using a transformer cube as the package.
RSV-SRV-0040
The report server encountered an internal error. Check additional information associated with this error message. If cause of problem cannot be ascertained, increase the logging level in the IBM Cognos administration tool and reproduce the conditions that caused the error. If the problem persists, see the problem determination information on the IBM Cognos Support Portal page at http://www-947.ibm.com/support/entry/portal/Overview/Software/Information_Management/Cognos_Business_Intelligence.
:(
OK... my example is using DMR, so that probably is why.
Does your Current Month data item work properly against your Transformer Cube? In my example, that was set to just allow a selection anywhere in the Time dimension of the sample package. The other data items are all relative to the member selected in Current Month, so get that to work and the Union and Excepts should work fine.
Rocky - never mind - I was using the (union) calculation in the slicer - as I had been doing for the past 6 months calculation. I removed the slices filter - and it worked !
thanks for your quick and consistent help...D