We have a date field that is either updated with todays date or a date in the past per the Cognos Datamanger ETL. The user wants this date field added to a cognos crosstab report. They dont want to see the value of date if its todays date though. We added a Query Calculation to the report and created an if then else statment to only show dates that are not today. We can not get cognos to accept it. The posting date is an integer in the sql data warehouse. Any suggestions? Here are the variations we have tried. Thanks in advance!
if [Stock Forecast].[Posting Date] = current_date
then 0
else
[Stock Forecast].[Posting Date]
****************************************
if [Stock Forecast].[Posting Date] = current_date
then null
else
[Stock Forecast].[Posting Date]
****************************************
if [Stock Forecast].[Posting Date] = cast (current_date), int)
then 0
else
[Stock Forecast].[Posting Date]
****************************************
case
when [Stock Forecast].[Posting Date] = current_date
then 0
else
[Stock Forecast].[Posting Date]
end
can you plz provide data in column Posting_date for eg 20131120 or 2012-12-13 etc
its 2013-12-07. Attached is a pic. Thanks!
if(cast( [Stock Forecast].[Posting Date],date) = current_date )
then
( null)
else
([Stock Forecast].[Posting Date])
Thank you for the information. It worked but now the user is asking for an additional criteria that should be taken into consideration. So in addition to it being the current date it also can't be ([Stock Forecast])].[Record Id] = '1'). This is a varchar field in the sql dw. We thought we could do this but it does not work. Any suggestions?
if(cast( [Stock Forecast].[Posting Date],date) = current_date )
and
(cast ([Stock Forecast].[Record Id], numeric) =1)
then
(null)
else
([Stock Forecast].[Posting Date])
if(cast( [Stock Forecast].[Posting Date],date) = current_date
and cast ([Stock Forecast].[Record Id],numeric)<>1)
then
(null)
else
([Stock Forecast].[Posting Date])
I am not sure u want [Stock Forecast].[Record Id] should be equal to 1 or is not equal 1
that worked, thank you!