Oracle Analytics Cloud and Server

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

If two columns match then one column needs to be hide dynamically in obiee 11g?

Received Response
92
Views
17
Comments
2»

Answers

  • Rank 6 - Analytics Lead

    Either you are very bad at explaining problems or comprehending then solutions suggested.

    Or both..

  • Rank 6 - Analytics Lead

    Not sure if are all missing what you're trying to explain or if you're just lacking the fundamentals to do this since it should be quite straightforward.

    Let's say you have 2 tables - 1 Fact table and 1 Dimension (Time) which would be something like this (very simple version):

    Dim

    DAY_KEYCAL_WEEKCAL_YEARDAY_OF_MONTH
    2010032312201023
    2010032412201024
    2010032512201025
    2010032613201026
    2010032713201027
    2010032813201028
    2010032913201029

    Fact

    KEYDATE_KEYREVENUE
    120100323120
    220100324452
    320100328752
    420100329401

    If you model this properly in the RPD, you should be able to build a simple analysis with only 2 columns: CAL_WEEK from the DIM and REVENUE  from the FACT and you would get something like:

     

     

    CAL_WEEKREVENUE
    12572
    131153

    And as others have been trying to explain to you, there's no need to create 52 or 53 columns since they you'll have either 52 or 53 weeks based on the data you have for a specific year.

  • Robert Angel wrote:I was not suggesting an 'exotic workaround' ...

    Your is clean, it's the original 53 columns with a FILTER inside what I call "exotic"

  • Do you know what a pivot is, right?

    A thing where you can select an attribute ("week" in your case) which will be display as many columns as there are values.

    So select the "week" and place it in the "column" part of the pivot view, place the measure as the measure and place the other attribute(s) (the one giving you multiple rows) as rows in the pivot and ... enjoy the magic.

    You select a year (or filter a year) and you will see as many column as you have of week that year.

    Do you always want to see 52/53 columns even where there is no data (like for 2018 which just started) ? Check the checkbox to make outer joins and and done.

    So far you are repeating the need again and again, did you try what has been suggested? Did you try the filter as Robert suggested or the pivot?

  • Rank 3 - Community Apprentice

    Hi ,

    Week file data have (weeks, weekend date, month, year, countries, sales, stocks). using odi  week file data loaded into database

    we have weekly data in database. it is not stored in day wise data.

    Thanks to every one, you people are helping a lot to me

    we have historical data in data base. 2000 to 2017 years data.

    If year is leap year there is no issue with daisplaying all 53 weeks data in report (our report is union report.  pivot table view)

    dispalying weeks data in 53 individual columns as our customer requested .(we have applied different formulas on 53 invidual columns for 53 weeks )

    facing issue with non leap year.

    Non leap have 52weeks .  there is no data for 53 week. even though 53 week column also appearing in report (column heading only)

    we have 53 individual columns .

    I am trying to here dynamically hiding last column when year is non leap year. when its leap year it should display 53 weeks data in 53 individual columns.

    Thanks in advance,

    Kavya,

  • Rank 6 - Analytics Lead
    Week file data have (weeks, weekend date, month, year, countries, sales, stocks). using odi week file data loaded into databasewe have weekly data in database. it is not stored in day wise data.

    It doesn't really matter how you have data in the file but it does matter what you do with it. Do you have a single database table with all this information or did you split the information in the file into different tables? How did you model this in OBIEE?

    I think what you're missing here is that the solution for your problem should be quite straight-forward if you have things modelled properly which doesn't appear to be the case and we're kind of stuck in a loop right now. I'd say it's probably better if you just describe how you have things in your database and in the RPD, otherwise you won't get much more help than what everyone already said.

  • Rank 8 - Analytics Strategist

    Kavya,

    "we have 53 individual columns ."

    - in your database, report or both?  any which way -- the experts are indicating that this is not the appropriate way to do it ... this is how we might have done it before the invention of modern reporting tools.

    "I am trying to here dynamically hiding last column when year is non leap year. when its leap year it should display 53 weeks data in 53 individual columns."

    - which is the wrong approach given the tool-set (OBIEE) that you are working with.

    So... the resistance is because you've asked the experts for help and they've given it -- the real help is showing you a different approach.

Welcome!

It looks like you're new here. Sign in or register to get started.