If you are unable to create a new account, please email support@bspsoftware.com

 

How do you filter dates dynamically within dashboard (e.g. current year)?

Started by pacificbeavs, 22 Oct 2019 03:20:07 PM

Previous topic - Next topic

pacificbeavs

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.

Deep750

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'

MFGF

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.
Meep!

CognosPaul

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.