Print Page - Rolling 6 months data

Title: Rolling 6 months data
Post by: raghunori on 11 Mar 2023 11:50:13 PM

Hello experts..I have a requirement where I need to show rolling 6 months data from the date user selects in IBM Cognos Analytics Dashboard. The dates are all month end dates in the form "YYYY-MM-DD". The formula i am using currently is:

case when Sheet1.Month_End_Date_formatted > _add_months (maximum (Sheet1.Month_End_Date_Format),-6) then Sheet1.Month_End_Date_formatted else null end

The problem with the above formula is when I select a date, it filters out and shows only one month of data relevant to the date i selected instead of 6 months previous.

Can anyone tell me how this can be accomplished? My report is in the crosstab form below

   2022-12-31   2023-01-31

opening balance

Title: Re: Rolling 6 months data
Post by: srmoure on 18 Apr 2023 07:38:28 PM

I would use [Date] Between A and B

Title: Re: Rolling 6 months data
Post by: cognostechie on 18 Apr 2023 10:27:17 PM

Quote from: raghunori on 11 Mar 2023 11:50:13 PM
Hello experts..I have a requirement where I need to show rolling 6 months data from the date user selects in IBM Cognos Analytics Dashboard. The dates are all month end dates in the form "YYYY-MM-DD". The formula i am using currently is:

case when Sheet1.Month_End_Date_formatted > _add_months (maximum (Sheet1.Month_End_Date_Format),-6) then Sheet1.Month_End_Date_formatted else null end

The problem with the above formula is when I select a date, it filters out and shows only one month of data relevant to the date i selected instead of 6 months previous.

Can anyone tell me how this can be accomplished? My report is in the crosstab form below

2022-12-31 2023-01-31

opening balance

Hi

Where is your 'formula' ? In a data item or a filter? Looking at the layout of your crosstab, it seems you want to display 6 months in the columns and opening balance in the row. If that is the case then you need a filter to filter the query for the rolling 6 months range and that should be:

[Date] >= _add_months ( ?pDate?, -6) and [Date] <= ?pDate? . pDate , in this case, is the name of the date parameter

Title: Re: Rolling 6 months data
Post by: raghunori on 27 Apr 2023 03:45:16 PM

Thanks a lot.

This logic involving parameters works in Cognos Reports.

How does it work in Cognos dashboard?

Any idea

Also, My selection is not a date but it is year.

If i select year 2023, it should give be from current_date till 6 months back

if i select 2022, it should give from June 2022 till dec 2022 (6 months)

Title: Re: Rolling 6 months data
Post by: dougp on 28 Apr 2023 11:26:57 AM

QuoteWhere is your 'formula' ?

Title: Re: Rolling 6 months data
Post by: raghunori on 29 Apr 2023 03:01:42 PM

[Date] >= _add_months ( ?pDate?, -6) and [Date] <= ?pDate?

My comment was on above formula

Title: Re: Rolling 6 months data
Post by: dougp on 01 May 2023 09:40:08 AM

We can't help you if you don't answer questions. I did not ask what the filter expression is. I asked where it is.

Title: Re: Rolling 6 months data
Post by: raghunori on 02 May 2023 06:50:09 AM

I apolozise but I am not sure I understood your question. Can you please elaborate on your question? what do you mean by "where is my formula"? I did not apply the formula yet because I do not know how to use parameters in the dashboard

Title: Re: Rolling 6 months data
Post by: dougp on 02 May 2023 06:59:08 PM

QuoteThe formula i am using currently is:

case when Sheet1.Month_End_Date_formatted > _add_months (maximum (Sheet1.Month_End_Date_Format),-6) then Sheet1.Month_End_Date_formatted else null end

"currently" implies it is already in use. Where is it?

COGNOiSe.com - The IBM Cognos Community

IBM Cognos Analytics Platform => Cognos Analytics => Dashboards => Topic started by: raghunori on 11 Mar 2023 11:50:13 PM