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
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 Posts
Re: Reporter : How to do cumulative sum ?2012-06-07T12:19:42ZThis is the accepted answer. This is the accepted answer.It 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])))