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

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Time Age Bucket Filters in FM

Started by Cognos91, 09 Jul 2017 09:05:12 PM

Previous topic - Next topic

Cognos91

All,

I need to a create time based aging filters (such as 30/60/90 days) in FM, based on 4 different dates provided in one dimension, such as Ship Date, Order Date, Release Date, and Entry Date (purely for example only).  The aging is calculated based on the current date.
For example, Ship Date (30 days) = Ship Date between current date and (current date + 30). This implies the same for other dates as well.

Current requirement is to use the aging filter on the dashboard to dynamically filter the  data based on the four dates.
There is a measure, that will be used on the dashboard to depict aging per the four dates. Thus, if a user selects 30 days, then the data will automatically, display the measure value for Ship date (30 days), Order Date (30 days), Release Date (30 days), an Entry Date (30 days). The issue is this needs to be done dynamically.

Currently, I created separate filter calculation in the FM model for each dates with different aging buckets. These are being as filter in the dashboard.

Is there a way to create an aging filter (with values 30/60/90) that can be used to filter data set for a measure based on the 4 dates respectively?

Thanks,

Cognos91

All -
rather than adding a filter, i tried creating a data item with case statement, that will add the respective age buckets for the 4 different dates based on the expression as listed above.
However, how will this data item work when used as a filter?
or can this one data item be used to filter the 4 dates respectively?

bdbits

I am not sure I entirely follow.

Usually, for "aging buckets", I create a table with the buckets in the data warehouse, then link facts to the appropriate bucket (row) at ETL time. The aging buckets table can then be used as any dimension would for filtering. Assuming ETL is daily, I would think that would work here.


Cognos91

Thanks for the reply. I created a dummy aging table and was able to test the same.
However, the issue is that the dates are attributes of the dimension and not the fact table.
Thus, if I need to ensure that the aging table can be used to filter the  4 dates, then I will need to create an alias shortcut of the dimension 4 times, one for each date.