Saturday, 9 November 2013

How do we calculate MTD, YTD in cognos




For Daily sales data item I have given as

case
when ( (extract(day,[SALE_DATE])=extract(day,?Parameter1?) ) and (extract(month,[SALE_DATE])=extract (month,?Parameter1?)) and (extract(year,[SALE_DATE])=extract (year,?Parameter1?)) ) then ([AMOUNT_SOLD])
else (NULL)
end

2) For Monthly sales data item I have given as
case
when ( (extract(month,[SALE_DATE])=extract (month,?Parameter1?)) and (extract(year,[SALE_DATE])=extract (year,?Parameter1?)) ) then ([AMOUNT_SOLD])
else (NULL)
end
3) For Yearly Sales I have given as
case
when ( (extract(month,[SALE_DATE])=extract (month,?Parameter1?)) and (extract(year,[SALE_DATE])=extract (year,?Parameter1?)) ) then ([AMOUNT_SOLD])
else (NULL)
end


No comments:

Post a Comment