Dynamic Rolling Average

Kelly Goldsworthy

I am looking to create a dynamic period, over which I can calculate a rolling average.

For example:

Daily usage period = 7

Average sales over period will then be from today, backward looking for 7 days.

 

The problem is that the period may be different for every sales location, and could be changed daily.

For example:

 

location: 1234      date: 17.05.2018      Daily usage period: 7

location: 1234      date: 20.05.2018      Daily usage period: 4

 

location: 9632      date: 17.05.2018      Daily usage period: 7

location: 9632      date: 20.05.2018      Daily usage period: 7

 

Any suggestions?

 

I have created an input cube for the daily usage period by location. 

Pietro Di Cino

ciao Kelly Goldsworthy the Daily usage period is calculated based on what entity?

Kelly Goldsworthy

Hi Pietro, the Daily usage period is a manual input and is determined by the model user. So therefore the entity is just

"Daily usage period". The logic behind why a user would change the period is driven completely outside of this model.

I hope that clarifies?

Pietro Di Cino

Have you tryed to use the Advance option in dataflow? You can move back or forth your values based on the cube where you made dataentry.

Unknown

Hi Kelly Goldsworthy,

 

if you need to calculate a dynamic Daily Sales Average based on a location, there are several ways - it would also depend on the granularity of the calculation, i.e. if you only want to see it in a report that is by specific location, or if you need a version that shows all the different locations and the daily sales average per location based on the settings you inputed.

I built a solution for a customer quite some time ago that might help you achieving what you look for, i'm attaching a quick example i reworked on a demo database. Not sure it's the best way but it does work nicely.

 

 

For every location, you can enter a number of days for your rolling average, and what is the actual last sales date for every location (that might differ due to timezones) - this can be populated from datareaders or dataflows alike

 

The core of the solution is a tech Date type cube, that contains the date itself for every day. this is needed in order to do date-based computations in a dataflow - it can be generated simply by extracting and reloading the day entity into a date cube by day.

 

What you need to do is a dataflow that calculates a target flag cube by Day and Location, where for every location we will have the days involved in the Average calc. In order to do that, we write a formula that does the following:

 

if( and(date > today - daysForAvg ; date <= today); 1; 0)

this will generate the map of days per location on the top right.

You simply then multiply your daily sales by that cube, and then generate a calculated daily sales average at the level of granularity you desire.

There are intermediate solutions that can work nicely based on the level of granularity at which the report runs, but by achieving this you can reach an high level of automation.

I've attached database and capsule with the example if you want to have a look at it. Can be opened with BOARD 10.1.4, the database can also be opened with previous versions in case.

 

Let me know if this is clear enough, hope it helps

Michele

Kelly Goldsworthy

Brilliant - thanks for this. I am going to start working on it now, but I think it is exactly what I was looking for.