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

Way to get Previous month booking

Received Response
327
Views
5
Comments
User_ON8FM
User_ON8FM Rank 2 - Community Beginner
edited Aug 13, 2024 5:20PM in Oracle Analytics Cloud and Server

Hi,

I'm very new to OAC and I'm finding trouble with the following case.

I have a situation: when the user selects the month in the prompt, he could able to see Selected month bookings, as well as previous month bookings (in two separate charts) in the dashboard.

How can I get previous month bookings dynamically? I searched for a function, but I didn't find any direct function or formula to create a measure.

Ex: If I have the following data, and if a user selects Mar in the 'Month' prompt, I could able to show Feb sales as well (in a separate performance tile chart).

  

Month Sales
Jan$1000
Feb$2000
Mar$3000

Thank You,

Krishna Kalyan

Answers

  • Joel
    Joel Rank 8 - Analytics Strategist

    It depends on a number of things. Do you have a proper time dimension? Is this being implemented in OBIEE?

    Typically, you'd need to use the TIMESTAMPADD function

    TIMESTAMPADD(SQL_TSI_MONTH, -1, add sales month date here)

    pastedImage_2.png

  • Gianni Ceresa
    Gianni Ceresa Mod

    What Joel said if possible.
    If you can't that way have a look at https://www.youtube.com/watch?v=DQ5nAIpUa8M , it can give you some ideas of possible workarounds.

    Also https://www.youtube.com/watch?v=8BdAB8ictvA  can give hints for a workaround.

  • User_ON8FM
    User_ON8FM Rank 2 - Community Beginner

    I'm using OBIEE. Using the above formula, I can get previous month timestamp.

    Actually, I need to display Previous month Value.

    Any help on this?

    I'm creating analysis and adding it to the dashboard which has Month and Year prompts. The measure should display the earlier month bookings data as per the prompt selection.

    I tried, using the workaround shared by Gianni. It's showing value in analysis, but in the dashboard, same chart shows '0.00' as value.

  • Gianni Ceresa
    Gianni Ceresa Mod

    If you are using OBIEE you must have a proper time dimension, right?

    In this case use the AGO function. (AGO only works with a dimension having the needed levels and configured properly as time dimension)

  • Jerry Casey
    Jerry Casey Rank 6 - Analytics Lead

    If you can prompt using a numeric month, name a presentation variable in the prompt, such as varMonth.

    Filter your query on "YourTable"."Month" =@{varMonth}{3} OR YourTable"."Month" =(@{varMonth}{3}-1)