Calculating Variance dymanically

3193829

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.

Manoj Dixit

how to get Value of previous row

Thomas Dodds

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

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

Your original post did not mention about -7 days?

Joel

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

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