Expression to return last 6 months

[jway]

I'm having trouble coming up with an expression in a relational report to return the last 6 months' data. So basically the maximum data in the fact table and then the 5 months before.  I have a data item MaxDate that has the most recent month. Then I tried a filter expression:

[Customer Integration].[By Customer Integration Post Date].[Month] >= _add_months([MaxDate], -5)

But that's not appearing to have any effect on the data displayed in the report.  Any ideas on what i can do to get this working?

[Cognos_Jan2017]

[Customer Integration].[By Customer Integration Post Date].[Month] >= _add_months([MaxDate], -5)

Do you have another Field/ Data Item other than '[Month]'?

Something like ...
[Customer Integration].[By Customer Integration Post Date].[DateOfInterest]

HTH, Bob

[jway]

We have other fields from the date dimension.  Date, MonthDayYearSlash, ShortMonthYear, etc.  In our dimension Month is in the format  YYYYMMDD, Date is: YYYY-MM-DD, ShortMonthYear is:  MAR 2018

[Cognos_Jan2017]

I have Authored only Relational Data, so we need a Dimension Person to help on this.

[jway]

It is a relational package that i'm using on this.

[Cognos_Jan2017]

You said " ... in our dimension Month ...

In my relational App, things like "Date Of Incident' are filtered (like 5 Months worth of data) based on 'Date Of Incident'.  Can you use that technique?

[Lynn]

You said " ... in our dimension Month ...

Data warehouses are designed with fact tables and dimension tables. A relational model is often produced over this dimensional data warehouse design. Any of the Ralph Kimball books on data warehousing explain these concepts.

The confusion comes because OLAP and ROLAP models are also referred to as "dimensional".

I'm having trouble coming up with an expression in a relational report to return the last 6 months' data. So basically the maximum data in the fact table and then the 5 months before.  I have a data item MaxDate that has the most recent month. Then I tried a filter expression:

[Customer Integration].[By Customer Integration Post Date].[Month] >= _add_months([MaxDate], -5)

But that's not appearing to have any effect on the data displayed in the report.  Any ideas on what i can do to get this working?

If the filter isn't filtering anything out then the issue could be with the [MaxDate] calculation. What is the expression you are using for that? Is the filter set to before or after auto aggregation? Can you view the tabular data to verify that you have data that should be filtered out?

[jway]

My expression for MaxDate is:   maximum([Customer Integration].[By Customer Integration Post Date].[Month])  It's set to Before Autoaggregation.  When i look at the tabular data Month=20161001 and MaxDate=20161001.  Is the problem that these values are not actual dates so the _add_months fails ?

[MFGF]

My expression for MaxDate is:   maximum([Customer Integration].[By Customer Integration Post Date].[Month])  It's set to Before Autoaggregation.  When i look at the tabular data Month=20161001 and MaxDate=20161001.  Is the problem that these values are not actual dates so the _add_months fails ?

Hi,

You figured out the issue here - your Month item isn't a Date data type, it's a numeric field. _add_months only works with dates, not with numbers.

Cheers!

MF.

[jway]

Changed my MaxDate expression to be:  cast(maximum([Customer Integration].[By Customer Integration Post Date].[Date]),date)

The filter expression is:  [Customer Integration].[By Customer Integration Post Date].[Date] >= _add_months([MaxDate], -6)

But I'm still seeing all the months available in the crosstab, not just the 6 most recent.  I'm not understanding what i've done wrong......

[sdf]

why not something like this

[Date] between current_date and _add_months(current_date,-6)