How to get the value of the last month in the quarter — Oracle Analytics

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

How to get the value of the last month in the quarter

Received Response
135
Views
7
Comments
BenFreez
BenFreez Rank 5 - Community Champion
edited Aug 13, 2024 6:06PM in Oracle Analytics Cloud and Server

Hi,

I''m trying to get the value against a quarter. I have 2 tables Time dimension and Fact table.

I want to display the value against each quarter with the specificity below :

Consider quarter Q1 it should display the value of 3rd month, Q2 should display value for 6th month etc...

And if in any particular Quarter we dont have a value for last month then it should display the value for 2nd month, if 2nd month value is also not present then it should display 1st month sales value.

Please can you guide me on the best way to realise this ?

Best regards,

Ben

Answers

  • Robert Angel
    Robert Angel Rank 8 - Analytics Strategist

    Best way to do this is to build it into your time hierarchy, especially if it is a corporate need that will be heavily used throughout your subject areas.

    To do this you would have a hierarchy as below

    Total ¬ Year ¬ Quarter ¬ Month ¬ Date (if applicable)

    Then merely by having your fact measure(s) defaulting to sum by including the quarter in a report with the measure in question you will get the functionality you describe.

    If it is a one of your could use some case logic to convert your months into quarters.

    case when Month in ('Jan','Feb','Mar')  then 'Q1'

    ---etc

  • BenFreez
    BenFreez Rank 5 - Community Champion

    Hi Robert,

    I tried to use the time hierachy as you mentionned but the problem is that the value of the quarter showed is the sum of Jan + Feb + Mar for Q1 and in my case i only need Mar for Q1.

    For example

    Q1 :

    - Jan : 100

    - Feb : 200

    - Mar : 450

    ==> I want to see only Mar (450) for Q1

    Q2 :

    - Apr : 15

    - May :45

    ==> I want to see only May(45) for Q2

    And i'm trying to do it dynamicaly for each year

    Best regards,

    Ben

  • Robert Angel
    Robert Angel Rank 8 - Analytics Strategist

    Sorry, missed your 'last' month requirement, for this you really need the quarter to be in your hierarchy then QTD (To Date measure built against the Quarter) measures would be ideal.

    You can probably also do it with case logic, but given your additional 'last month that has data in the quarter' then what I describe is definitely better.

  • BenFreez
    BenFreez Rank 5 - Community Champion

    Hi Robert,

    Thanks a lot for this solution, i will test it and come back to you to confirm if it's solving my problem

  • Robert Angel
    Robert Angel Rank 8 - Analytics Strategist

    Note also that in the aggregation options on your fact measures you have first / last. This would not work though if your (say) March was null in Qrtr1 and you wanted February that isn't...

  • Walter de Wit
    Walter de Wit Rank 2 - Community Beginner

    Perhaps you can work something out with RMAX() by year, quarter. Maybe this will solve your puzzle if the toDate() function is not doing what you expect from it.

  • BenFreez
    BenFreez Rank 5 - Community Champion

    Hi Walter !

    Exact the RSUM() function is also very interesting for my need.

    THanks a lot for your help

    Ben