This discussion is archived
1 Reply Latest reply: Aug 20, 2012 3:21 PM by 938723 RSS

Cumualtive Sum over User Defined Time Dimension

938723 Newbie
Currently Being Moderated
Hoping that someone could explain how to use an Olap expression to determine the cumulative sum over a hierarchy.

I have a Date Dimension with levels:

DAY,WEEK,MONTH,YEAR

And a Time dimension with levels:

ALL_TIME,HOUR,MIN

How can I perform a cumulative SUM of a measure over my Time Dimension?

I get the following error:

An error has occurred on the server
Error class: Express Failure
Server error descriptions:
DPR: cannot create server cursor, Generic at TxsOqDefinitionManager::generic<CommitRoot>
INI: XOQ-02000: PrimaryDimension "SH_OLAP.TM" is not a valid TimeDimension for the time series expression., Generic at TxsOqSyntaxPrinter::checkIsTimeDimension

With the following expression.

SUM(SH_OLAP.SALES) OVER (HIERARCHY TM.STANDARD BETWEEN UNBOUNDED PRECEDING AND CURRENT MEMBER WITHIN LEVEL)

TM in this case is my Time dimension.
  • 1. Re: Cumualtive Sum over User Defined Time Dimension
    938723 Newbie
    Currently Being Moderated
    Well it seems that the solution to this is to simply populate the TIME_SPAN and END_DATE fields.

    My Time dimension uses minute and hours, and therefore I am using a TIME_SPAN of 1/1440 for minutes and 1/24 for hours.

    My END_DATE is based on a TIMESTAMP type (rather than DATE) and I am using 01-JAN-2000 for my date portion and the appropriate time portion.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points