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

Comparing date columns for analysis

Received Response
34
Views
4
Comments
Odarin
Odarin Rank 3 - Community Apprentice
edited August 13 in Oracle Analytics Cloud and Server

Hi all.

I have the table with some order information (id, description, order date, ...) and I have a time data-table for using as dimension with info about years, months and its start and end dates.

ORDER_ID      ORDER_DATE                                    YEAR      MONTH      START_DATE      END_DATE

id1                    15.03.2018                                           2018       JAN-18           01.01.2018      31.01.2018

id2                    25.02.2018                                           2018       FEB-18           01.02.2018      28.02.2018

id3                    03.09.2018                                           2018       MAR-18          01.03.2018      31.03.2018

  (.......)                                                                                     (......)

I need to create the analysis that would be show me my orders allocated by periods like in example below.

My main question is how can I compare order date with month's start-end dates and display in hierarchy, how to put order date between two period dates, should I use some formula or filter or I need to do it on database level, adding a period column to order table?

YEAR      MONTH      ORDER      ORDER_DATE

          (....)

2018      FEB-18           id2           25.02.2018

2018      MAR-18          id1           15.03.2018

          (....)

2018      SEP-18           id3           03.09.2018

Answers

  • Joel
    Joel Rank 8 - Analytics Strategist

    DO you have an Order date dimension in you repository? With a date dimension (as well as a logical dimension with a hierarchy), you can add all date attributes (quarter, month, year, week etc) you require to enable the type of analysis you need to perform.

  • Gianni Ceresa
    Gianni Ceresa Mod

    Is your question related to the front-end or the RPD?

    If the front-end you will need to fix your model as you missed a join, if it's in the RPD your date dimension would ideally need a day column to make a 1-1 join. If you can't have the day grain in your date dimension just write the join with a BETWEEN:

    ORDER_TABLE.ORDER_DATE between DATE_DIM.START_DATE and DATE_DIM.END_DATE

    (this is a physical join obviously)

  • Odarin
    Odarin Rank 3 - Community Apprentice

    Joel, Gianni, thanks for your answers!

    As I understand, the best way is to update date_table and add days columns there to make join with order date. But then I have one more question (perhaps very lame, sorry!)  - now I have only one date dimension build on my date_table. I'll make 1=1 join with order date by day and if I'll like to create analysis not with order date but with, for example, payment date (which also stored in order_table), should I make new time dimension build on date_table alias joined with that payment_date (and act that way every time I need to build an analysis with other date)?

  • Gianni Ceresa
    Gianni Ceresa Mod

    Different business date (from order to payment) = different point of view.

    You either create another time dimension proper to payment date or you create different measures proper to payment date.

    OBIEE is about modelling things, so you need to represent all those things as models being star schemas.