Hi I am using a value prompt and query with _add_months (Current_date,-3) but of course this just gives me the month that is 3 past.
How can I get value prompt to show current month OCT and SEPT, AUG, JULY?
Put a filter in the query of the prompt to show the show the dates within that range.
[Date] >= _add_months( current_date, -3) and [Date] <= current_date
omg so simple thank you.... my issue now is I have the months repeating for every date.... how do I get to just show 1 version?
[First POL ETA Date]>= _add_months( current_date, -3) and[First POL ETA Date]<= current_date
You have to use the query item which provides the name of the month, not the date. The filter on the date is ok. Cognos will automatically use Select 'distinct' on the item and thus would show the name of the month only once.
If you don't have an item with name of the month then you can create one with a Case statement..
Case
When extract(month,date) = 1 then 'JAN'
When ....