grand total on time series metrics

3577602

Hello,

I have created multiple time series metrics in RPD and i also get right results when i am using these columns in answers.

But whenever  i add grand totals in the report ,getting below error

[nQSError: 22036] Level in ToDate function (Fiscal Month) must be a rollup of (or equivalent to) the measure level (Fiscal Year)

If i change the aggregation rule to sum ,then grand totals are showing up on timeseries metrics.

changing aggregation to sum is easy but when i do the percentage calculations using time series functions and want to see the percentage in grand total i have to caluculate as "weight/sum(weight MTD by month)" and then define server complex aggregate in the aggregation rule.this is fine when i have one dimension attribute,but when i have more than 10 attributes it is lot of work.

i can get the results i want  but it is not straight and easy.Power users find it tough to understand it.

is there anyway i can specify aggregation on time series metrics as i think since obiee server doesnot know how to aggregate time series metrics,it is throwing me the above error.

Please let me know if i am missing anything here.

Robert Angel

Hi,

if your time dimension and corresponding time dimension hierarchy are set up then it should be as easy as; -

1. Duplicate existing SUM measure currently not associated with a time 'level'

2. Drag the duplicated measure to (say) Year on the dimension hierarchy to get a Total by Year - which can be used at (say) month level as a value which is invariant except on change of year

3. Rename as something meaningful (say) Total 'X' by Year

and repeat at month, quarter etc etc level

You should also be able to drag to the Total level for a completely invariant measure, which completely ignores TIME and gives you the total of ALL time.

To get your percentage measure you would then create a LOGICAL measure which used the existing unbound measure / Total by Year (or whichever).

3577602

i have created time series metrics and they are working fine,but when i put grand total on a table view,getting below error:

[nQSError: 22036] Level in ToDate function (Fiscal Month) must be a rollup of (or equivalent to) the measure level (Fiscal Year)

is this expected error or do I need to do anything different in the rpd

Gianni Ceresa

Hi,

Let's go back to the beginning ...

First of all: what's the exact version of your OBIEE? (including bundle patch)

So everything is fine except your grand totals and to make it work you need to switch the "Aggregation Rule (Total Row)" from "Default (Sum)" to "Sum"?

Or by default you have something else?

What's the structure of your analysis? (to understand the dimensionality involved in the issue) What's the level of the "todate" column ?

3577602

OBIEE VERSION: 12.2.1.0.0

i created below table showing weight and weight month to date (defined at fiscal month level) . Both metrics are showing same number as expected

when i add grand total , i get below error unless i specify aggregation rule as sum for MTD metric in the analysis. Is this expected?

in the aggregation rule , i see below options and i have change it to sum

Gianni Ceresa

Are these things measures? Or attributes?

What's the aggregation rule in the RPD?

3577602

measures in rpd. aggregation is sum on weight metric and i used the same column to calculate MTD using TODATE function.

i dont see "Default(Sum)" as aggregation rule option on MTD measure.

Robert Angel

Are the Fiscal Month and Fiscal Year in the same dimension hierarchy?

i.e. Do you have something that looks like this; -

Total ¬ Fiscal Year ¬ Fiscal Quarter ¬ Fiscal Month ¬ Detail

Where ¬ is shorthand for 'is the parent of'

Reason I ask, your measure is Month to Date, not Fiscal Month to Date AND your error message seems to indicate that Fiscal Month is not below Fiscal Year in the hierarchy

3577602

my date hierarchy looks like this

Total ¬ Fiscal Year ¬ Fiscal Quarter ¬ Fiscal Month ¬ Detail

And also MTD number gives me right number. I get error only when I add grand total on timeseries metrics

Robert Angel

Have you also checked your time hierarchy's consistency of number of elements at level?

Do the number of elements increase as you go down the hierarchy from Total to detail?

3577602

yes,the number increases as i go down the hierechy in the rpd.

i have 2 hierarchies defined for calendar and fiscal calendar on date logical table.do you think that would have any impact?

let me know if we need to specify aggregation rule for a time series metric in analysis or will the tool use the aggregation on the base metric on which time series metric is built?