Quote from: IanDev on 21 Nov 2018 09:54:41 PM
Hi
I'm new to Cognos Analytics, so this is a newbie question.
I want to create a date as a reference date to use in calculations in the report. Predominantly to check the report calculations will work for different dates and check the results.
So I have used calculations from another report which uses current_date.
For example
lastYear : extract(year,_add_years(current_date,-1))
So I want to change current_date to a value.
1. Is there a criteria for entering a date in a query expression? i.e. extract(year,_add_years(31/10/2018,-1))
2. Could I use a prompt ?
3. Could I create a query calculation and just put in that date? I've tried this but errors when I use it in another query calculation (not sure if it is recognizing it as a date because of formatting).
I'm coming from Tableau - where I can create a parameter and use that in the calculation - but it looks like parameters in Cognos have different functionality.
Thanks IanDev
Cognos Analytics 11.0.11
Hi,
Let's take these in order:
1. Yes - use yyyy-mm-dd format, eg extract(year,_add_years(2018-10-31,-1))
2. Yes. You can either use a simple parameter and a date prompt, or a prompt macro and a date prompt.
An example of the former would be extract(year,_add_years(?MyDateParameter?,-1))
An example of the latter would be extract(year,_add_years(#prompt('MyDateParameter','date','2018-10-31')#,-1))
3. Yes. Use the date format from 1 above, and it will be recognised as a date.
Cheers!
MF.