Date Critera in Query Expressions

[IanDev]

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

[MFGF]

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.

[IanDev]

Thanks MF
Excellent response.  I learnt more in 5 lines than half a day searching on the internet.
Cheers IanDev