Oracle Analytics Cloud and Server

Home› Oracle Analytics› Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Measure aggregate formula ommit filters

Received Response
22
Views
5
Comments
User_KHPMG
User_KHPMG ✭
edited August 13 in Oracle Analytics Cloud and Server

Hi all,

I need to calculate an aggregate in an Analysis but I have the following issue. What I need is the last column measure "NC Todas MES". It is the Total Result for Month (Año-Mes). I have calculated it with an AGGREGATE AT or SUM BY expression.

So, this is correct:

Aggregate_1.jpg

But if I filter for some "Dpto.NC", for example:

Aggregate_2.jpg

This is the new Result:

Aggregate_3.jpg

What I need is the filtered result also for "NC Todas MES". It would be "5" for 201601 and "5" for 201602.

Could someone help me with this?

Thank you!!

Answers

  • Andrew Fomin.
    Andrew Fomin. ✭✭✭✭✭

    This is an expected behaviour: Logical SQL Reference - 11g Release 1 (11.1.1)

    AGGREGATE AT

    This function aggregates columns based on the level or levels you specify. Using AGGREGATE AT guarantees that the aggregate for the measure always occurs at the levels specified after the keyword AT, regardless of the WHERE clause.

    Try to use Aggregate("measure" by "table name"."Año-Mes") instead.

  • User_KHPMG
    User_KHPMG ✭

    Thank you Andrew,

    I know this is an expected behaviour, but I need to know how to get the result that I need.

    Using both, AGGREGATE AT or "Aggregate("measure" by "table name"."Año-Mes") the result is the same.

    The calculation that I need has to be done after applying the filters to the analysis.


    Any other idea?

  • Andrew Fomin.
    Andrew Fomin. ✭✭✭✭✭

    Aggregate(... by ...) and aggregate(... at ...) don't work the same. Aggregate AT ignores LOB filter, Aggregate BY - not.

    aggregate.PNG

    It is possible that I don't understand what is the problem. Try to reproduce it with the default sample model so it's possible to discuss it more precisely.

  • User_KHPMG
    User_KHPMG ✭

    Thank you Andrew,

    You are right, I've reproduced your same example with the sample model and I can see the difference between AggregateBY and AggregateAT.

    Taking my own model, I have realized that with other attributes in the dimension (defined in a different level) is working correctly, but I cannot reproduce the difference using the attribute "Dpto.NC", the result is the same in both formulas.

    So, I'm going to check the model, it must be something there.

    Thank you again!

  • Andrew Fomin.
    Andrew Fomin. ✭✭✭✭✭

    I'd recommend you to start with hierarchy keys check. From my experience, incorrectly defined keys are the most often cause of the strange behaviour like this.