Oracle Analytics Cloud and Server

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

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

SUM values from last 3months for each branch

Received Response
191
Views
7
Comments
choracy69
choracy69 Rank 6 - Analytics Lead
edited August 13 in Oracle Analytics Cloud and Server

Hi,

I have problem.

In analysis I have columns like: Year, Months and measure.

Measure is MEASURE_1#1 from rpd and it has MAX aggregation (set in rpd).

It look like:

pastedImage_0.png

Now I want to add second columns and create formule where:

for 2017 M12 I want SUM (m10+m11+12), for 2017 M11 I want sum (m09,m10,m11) etc.

How can I do this?

Answers

  • Robert Angel
    Robert Angel Rank 8 - Analytics Strategist

    You need to use time series function - period rolling - see below link.

    OBIEE: Time Series Functions OBIEE

  • choracy69
    choracy69 Rank 6 - Analytics Lead

    Ok, I have MEASURE_1 with MAX aggregation and when I'm using PERIODROLLING like: PERDIOROLLING(MEASURE_1, -2,0) in results i see MAX VALUE from 3last months.

    pastedImage_0.png

    I must set max in rpd  but on analysis I want to sum last 3months

  • Robert Angel
    Robert Angel Rank 8 - Analytics Strategist

    Sorry, maybe being slow, suffering from man flu here - are you asking for sum of last 3 max values?

    Or do you just want sum of last three months?

  • choracy69
    choracy69 Rank 6 - Analytics Lead

    Yes, I am asking for sum of last 3 MAX values.

    My mistake.

  • Robert Angel
    Robert Angel Rank 8 - Analytics Strategist

    Would this work for you - the principal of it and the underlying database; -

    https://sonra.io/2011/06/01/time-series-in-obiee-analytic-functions-and-range-window-as-an-alternative-to-obiee-ago-func…

  • Gianni Ceresa
    Gianni Ceresa Mod

    Because of MAX you need to do the sum yourself, so all you need is to use AGO twice to get the M-2 and M-1 value and add the current one.

    So your formula will be <measure> + AGO( measure M-1 ) + AGO( measure M-2 )

    this isn't a valid syntax, it just gives you the idea on how to do

  • Thomas Dodds
    Thomas Dodds Rank 8 - Analytics Strategist

    you want 3-month running sum of max values by month ...

    you've got the max already so do an MSUM(max,3) and make sure your months are sorted correct ...

    Example

    This example shows a query that uses the MSUM function, along with example query results.

    select month, revenue, MSUM(revenue, 3) as 3_MO_SUM from sales_subject_area

    Result:

    MONTH    REVENUE    3_MO_SUM

    JAN      100.00     100.00

    FEB      200.00     300.00

    MAR      100.00     400.00

    APRIL    100.00     400.00

    MAY      300.00     500.00

    JUNE     400.00     800.00

    JULY     500.00     1200.00

    AUG      500.00     1400.00

    SEPT     500.00     1500.00

    OCT      300.00     1300.00

    NOV      200.00     1000.00

    DEC      100.00     600.00

    NOTE:   M01/JAN or ANY first value MSUM(Measure,#) = Measure ... keep that in mind if you are looking to bring forward an MSUM for your MO1/JAN