Most of my reports rely on dynamic dates (e.g. current year, previous year, previous two years, etc.), however I only see the option to filter for specific dates/years in dashboards. I'm guessing I could create a data module that limits the data dynamically, but is there a way to do this in the dashboard instead?
I think that's clear, but if not then here's an example. I have a "Sold Date", and I want to limit the dashboard to "Sold Date" within the last two years. In a standard/active report I'd simply do something like "Sold Date >= _add_years(current_date, -2)", but this doesn't appear to be possible in dashboards.
Believe you can create a calculation with a case statement, and add the calculation to the filter
eg.
case when Sold Date >= _add_years(current_date, -2) then 'Include' else 'Exclude' end
then add this to global filters and select 'Include'
Quote from: pacificbeavs on 22 Oct 2019 03:20:07 PM
Most of my reports rely on dynamic dates (e.g. current year, previous year, previous two years, etc.), however I only see the option to filter for specific dates/years in dashboards. I'm guessing I could create a data module that limits the data dynamically, but is there a way to do this in the dashboard instead?
I think that's clear, but if not then here's an example. I have a "Sold Date", and I want to limit the dashboard to "Sold Date" within the last two years. In a standard/active report I'd simply do something like "Sold Date >= _add_years(current_date, -2)", but this doesn't appear to be possible in dashboards.
If you do go down the Data Module route (and if you are using 11.1.x) you can set up Relative Time to cater for all sorts of requirements like this.
https://www.ibm.com/support/knowledgecenter/en/SSEP7J_11.1.0/com.ibm.swg.ba.cognos.ca_mdlg.doc/c_ca_set_rel_date_analysis.html
Might add more flexibility to your dashboard options if you do this?
Just a thought...
MF.
Even if you don't use data modules, a relative time dimension table is absolutely the way to go.
The table would have a Relative Time name (which could change), relative time key, and the date key for every date in that time range. Joined it onto the date dimension. It will snowflake the model, but it should still work fine.
The benefit is two fold, not only can you always filter on the relative dates, but you'd be able to compare relative dates in a single query.