Rolling 6 months data

[raghunori]

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      

[srmoure]

I would use [Date] Between A and B

[cognostechie]

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

[raghunori]

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)

[dougp]

Where is your 'formula' ?

[raghunori]

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

My comment was on above formula

[dougp]

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.

[raghunori]

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

[dougp]

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

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