Average & price difference calculation dynamically based on latest dates

Hello guys,

I need your help, to calculate the average for given data based on latest dates, for example need to find 5day average, 20 day average so workflow should pick the period dynamically the latest 5days & 20 days respectively, similarly need to find price change for given items like 1 day change in price (latest one day) and 5 day , 20 day change in prices for latest period.

kindly help me with work tools. Also jus to highlight, Input data contains for certain periods ( historical) and every day we add the latest data..so these conditions should be picked dynamically based on latest date available in date.

Thanks in advance for your help.

Sample Input & ouput.xlsx

Dynamic Processing

Data Investigation

Accepted answers

hillard

Perfect, thank u so much

All comments

morr-co

Hi @hillard - attached is a sample workflow. It leverages the Multi-Row Formula tool to look at the value in subsequent row. You can build off of this, increasing the Num Rows value to 5 or 20 to create the necessary calculations. The formula for 20 days would understandably be long. As an alternative you could consider using a macro to iterate through the records.

Screen Shot 2020-09-30 at 2.15.05 PM.png

avg_price_qty_diff.yxmd

hillard

Thank you so much for the quick response!, I'll check the workflow.

also, when you say macro, Is it inbuilt macros available in alteryx or user has to build it? I dont have knowledge in macro building.

hillard

I have chcked the workflow, it looks perfect for 1day changes.

how to create 5day or even 20 day changes in similar fashion ? I dont have an insights into macros.

Thanks again for your support, much appreciated!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703