If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

How to calculate YTD for Crosstab?

Started by ajith, 28 Dec 2006 10:37:03 AM

Previous topic - Next topic

ajith

Can anyone let me know how to calculate YTD data in a crosstab for a relational data source?

sir_jeroen

is there more than one year in your date selection or is your date limited to the YTD timespan?
If there's more than one year you could use a case when  year(date) = 2006 then (value) else (0) end

ajith

Oh! sorry I missed that information

Fiscal Year starts from April 1 and ends on March 31. The user has the ability to choose Month and Year at the prompt and he can choose any year from 1985 to 2020.
Suppose the user chooses Feb 2004 in the prompts then the report should display data from April 1, 2003 to Feb 29, 2004. Is it as easy as it sounds????

Thanks.

sir_jeroen

so:
step 1. Convert the year and month to a valid date if possible
step 2 create a  date_from (e.g. ?year? -1 ) and  a date_to based on this created valid date
step 2: filter your data on " where date between date_from and date_to "
step3: just sum all data and now you have all data for a ytd.