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

Format date YYYY - WW

Received Response
222
Views
6
Comments
AudreyB29
AudreyB29 Rank 1 - Community Starter
edited August 2024 in Oracle Analytics Cloud and Server

Hello,

I need your help, i can't find anywhere a solution to my problem.

I use OBIEE 12.2.1.0 for create dashboard.

I have a date on this format : "DD/MM/YYYY" and I try to have this format : "YYYY - WW"

WW = WEEK_OF_YEAR

I try to have this transformation in "Edit formula" in my columms selected when I create a rapport (fx).

I hope someone could help me

Thank you in advance for watching my message!

Answers

  • Joel
    Joel Rank 8 - Analytics Strategist

    cast(year(current_date) as char(4))||' - ' ||cast(week_of_year(current_date) as char(2))

    replace current_date with your date column

  • AudreyB29
    AudreyB29 Rank 1 - Community Starter

    Thank you very much for your answer !! And it works !!

    Now I have another problem, some dates appear: 2017-1 instead of 2017-01 how to get around the problem?

    I would like to present these dates in a diagram in chronological order and without the '0' it distorts everything.

    Thank you again for your answer !

  • Terna
    Terna Rank 2 - Community Beginner

    add ||case when week_of_year(current_date)<10 then '0' end|| 

  • Joel
    Joel Rank 8 - Analytics Strategist

    cast(year(current_date) as char(4))||' - ' ||CASE WHEN LENGTH(cast(week_of_year(current_date) as char)) =1 THEN '0'||cast(week_of_year(current_date) as char(1))   ELSE cast(week_of_year(current_date) as char(2)) END

    If correct, mark the answer as correct for other users of the forum.

  • Thomas Dodds
    Thomas Dodds Rank 8 - Analytics Strategist

    +1 to the answers given.

    Next time have a properly formed date/calendar dimension and you won't ever have to do this formula again.

  • AudreyB29
    AudreyB29 Rank 1 - Community Starter

    Perfect !!!!! Thank you very much for your help!!!!

    Here is the final solution in case other people have the same problem:

    CAST (YEAR(current_date) as char(4)) || case when WEEK_OF_YEAR(current_date) < 10 THEN '-0' ELSE '-' END || CAST (WEEK_OF_YEAR(current_date) as char(2))