Oracle Analytics Cloud and Server

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

Calculating Variance dymanically

Received Response
21
Views
6
Comments
3193829
3193829 Rank 5 - Community Champion

Hi All,

I have requirement to calculate Day over Day Variance. And user can select the Date  from the prompt, and i need to calcualte previous day of it to use in my formula.

Right now i am using the below formula, it is causing performance issues as i have nearly 20 columns with the same calculations.

DoD sales = Filter(sum(sales) using Date={today}) - Filter(sum(sales) using Date={previous day}).

Is there any other easier way of doing this so that i dont see performance issues.

Answers

  • Thomas Dodds
    Thomas Dodds Rank 8 - Analytics Strategist

    If you have the day key in your day dimension for the previous day ... you can alias your fact for previous day  ... then via your model you can have current and yesterday measure and then do the variance and % change.  You can also do this via time-series functions in the RPD if you have a properly formed chronological dimension hierarchy.

    PS:  for performance move as much 'wprk' back to the database ... functions in answers perform worse than functions in RPD which perform worse than manipulating data in the database.  Thus I suggest doing it with joins first (it performs the best).

  • 3193829
    3193829 Rank 5 - Community Champion

    Hi Thomas,

    I have dashboard prompt on Date. When the user selects the date, I need to calculate previous day and -7 days and use both of those days to perform calculations in the report.

    Can i use request variables for doing this?. I dont have time dimension hierarchy. I just have a date column and i have to use it for calcuations.

    is there a way to eliminate the performance issue. Can i get the user selected date into a request variable to the rpd.

    Thanks,

    Kalpana

  • Manoj Dixit
    Manoj Dixit Rank 6 - Analytics Lead

    Your original post did not mention about -7 days?

  • Joel
    Joel Rank 8 - Analytics Strategist

    This scenario tends to crop up quite frequently in this forum. If you want to perform trend analysis, you can only do this properly if you have a well defined date dimension in your underlying data warehouse which is in turn modelled correctly in your repository with a chronological dimension and hierarchy configured.

    You could in theory do what you want without the date dimension but you will run into performance issues (as you've already discovered).

  • 3193829
    3193829 Rank 5 - Community Champion

    yes Manoj, if i get -1 right i can use the same for -7.