Hello Cognos Guru's,
I have a cross tab. Lines : company, Columns : months and Revenue as mesure.
It works fine to show revenue by company and by month but I want a culative sum. It means that revenue for february is : January + february revenue. March is : January + February + March.
How can I do ?
Many thanks in advance for your help
This topic has been locked.
1 reply Latest Post - 2012-06-07T12:19:42Z by SystemAdmin
Pinned topic Reporter : How to do cumulative sum ?
Answered question This question has been answered.
Unanswered question This question has not been answered yet.
Updated on 2012-06-07T12:19:42Z at 2012-06-07T12:19:42Z by SystemAdmin
SystemAdmin 110000D4XK214 PostsACCEPTED ANSWER
Re: Reporter : How to do cumulative sum ?2012-06-07T12:19:42Z in response to SH9F_ROUSSEAU_JBIt will depend on the type of package you are using. If you are using a purely relational package then there are many ways to go about doing this. I would suggest that you look over the information in the documents on the Proven Practices site regarding relative time modelling. These documents will show you ways to generate the join which will result in a year-to-date aggregate value:
If you have a dimensionally modelled relational (DMR) or cube (OLAP) package then you can make use of the dimensional functions to handle this task much more easily. The periodsToDate function can be used to find the relative year-to-date range of members and the aggregate function can be used to roll them up into a summary value:
aggregate(currentMeasure within set periodsToDate([Cube].[Time dimension].[Time hierarchy].[Year level], currentMember([Cube].[Time dimension].[Time hierarchy])))