hi fellows,
I was requested to aggregate the data as per the screenshot:
cells of 'current result' row are calculated as: (up-to-date aggregated sum of metric 1) - (up-to-date aggregated sum of metric 2)
cells of 'shall be' row are calculated as: up-to-date aggregated ((up-to-date aggregated sum of metric 1) - (up-to-date aggregated sum of metric 2)) --- hope that makes sense : )
the question is: how to get such result in OBIEE? additional difficulty is that this shall be done at RPD level. What you see at the screenshot is not an actual report, rather some data that allows you to understand what kind of aggregation I want to implement. The actual report is UNIONED report of many rows with specific format and, for example, I cannot add months there (then I doubt RSUM can be used). From screenshot above I'd only see 1 cell with '16' value there.
I thought that nesting periodRolling would be the solution, the problem is that this isn't allowed - I can create logical column that uses 'Derived from existing columns' formula, where both, that formula & formulas of existing columns use PeriodRolling function, but then it fails when used at the report returning error:
State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. (HY000)
State: HY000. Code: 43113. [nQSError: 43113] Message returned from OBIS. (HY000)
State: HY000. Code: 43119. [nQSError: 43119] Query Failed: (HY000)
State: HY000. Code: 42023. [nQSError: 42023] Illegal aggregate nesting in query. (HY000)
any help would be appreciated, thanks in advance
P.S.:
OBIEE version: 12.2.1.4.0 (Build BIPS-20200101165857 64-bit)
DB with data: Oracle 11.2.0.3.0