What is an efficient way to calculate rolling average in a data flow?

Ken Walsh
Ken Walsh Employee
Third Anniversary Level 100: Foundations of Building in Board Level 200: Building A Planning Solution in Board Photogenic
in Platform

Hi experts,

I am reaching out to seek opinion on the most efficient way to calculate a rolling average based on current and prior months values in a data flow. Curious to know if using the time function period offset -1, -2, -3 etc. can be the only way to calculate this or if peope have found other ways that are maybe more efficient?

I have attached an example screenshot from a data flow step for reference.

Thanks

Rolling Average Data Flow Calculation.png

Accepted Answers

  • Alexander Kappes
    Alexander Kappes Employee
    Fourth Anniversary 250 Likes 100 Comments 5 Answers
    Answer ✓

    Dear Ken,

    thanks for your question.

    I think you solution is the moste common way to performa a rolling average calculation.

    Alternatively you can think about using Analytical Functions inside the Block Configuration. But I think the period offset has the better performance and is working dynamically.

    image.png

    Hope it helps.

    regards

    Alex

  • Leone Scaburri
    Leone Scaburri Employee
    Fourth Anniversary 100 Comments 5 Answers 25 Likes
    Answer ✓

    Hi @Ken Walsh,

    what about the Yearly Moving Average?  This function can be combined with the Cycle option to create different rolling periods on which the average should be calculated. For example, on a monthly InfoCube, setting the cycle to 4 returns the cumulated value on the past four months divided by four, i.e. the average on a four month rolling range.

Answers

Categories