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

Conditional Format Across Columns

Received Response
13
Views
5
Comments
User_2NI9B
User_2NI9B Rank 3 - Community Apprentice
edited Aug 13, 2024 5:47PM in Oracle Analytics Cloud and Server

Hi Everyone.

So, I need to compare a month with its previous month and do the format accordingly. Green means it grew, red means it dropped. How can we achieve this?

Thanks

                     

Region201701201702201703
West10011090
East200290400

Answers

  • Robert Angel
    Robert Angel Rank 8 - Analytics Strategist

    Create a formula based column as; month - previous month (full syntax from the formula browser, I usually copy one column and then just minus the other by copying its formula in)

    Create a conditional format based on this column which changes the format on the other column based on the value <=0 >0 (or whatever)

    Hide the formula based column

    Job done!

  • User_2NI9B
    User_2NI9B Rank 3 - Community Apprentice

    Would you please elaborate a bit? Not sure if I understood. My report has filter for three months that are fed by dashboard prompt.

  • Robert Angel
    Robert Angel Rank 8 - Analytics Strategist

    Okay, breaking it down, do you have a Previous Month Measure in your rpd, if so you are using that to calculate month - previous month, if not then you first need to create the equivalent (look into AGO functionality) and then apply that to three columns.

    My logic in the first post is basically explaining how to do it for one column.

    Personally I like time series graphs for this, upward and downward tell a better story, but then I am colour blind so maybe I am biased....

  • Thomas Dodds
    Thomas Dodds Rank 8 - Analytics Strategist

    You should have a basic set of logical metrics for each measure you have ... use of information drives data design.

    When a client says they need 'Sales' ... my response is "by what over what?"

    From there if I have a simple "by Employee over Month (monthly)" then I automatically build:

    Sales
    Sales Year-to-Date
    Sales Month over Month (with variance and percentage change)
    Sales same Month Last Year (with variance and percentage change)

    Sales Contribution

    etc...

    These are logical so should be built in the BI layer (RPD) and not on the front end ever.  If you need performance, then push these back to the physical database (and take the hit in ELT processing over-night).  NOTE:  if you do this on the front end - how will you color January given that you've got no December shown?  These complementary metrics would also be on the same time grain as your base measure as they are logical.

    Once you have all these ... it's easy to display them or use them to drive a lot of informative and actionable insights.

    Also, @Robert Angel has pointed out a fundamental in data presentation -- choose wisely as you want the user (any user) to get the same actionable conclusion at first glance.  Having been involved in many many BI endeavors - I know the value of what he's counseled.  If you insist on red/green add a black indicator as well (up/down arrows; +/-); you'll reach a larger audience and potentially one that you don't even have looking at your stuff at present. 

  • Teresa Modesta
    Teresa Modesta Rank 5 - Community Champion

    Hi sweetie,

    word from one who has been there recently - do mark their help as helpful / answered and / or close your question if you ever want to ask another question again on these boards...

    If they have not answered your question or you do not understand their answer then do give them more detail, they can only do so much working in the dark...

    First time I didn't I nearly got a smacked bottom...