Average & price difference calculation dynamically based on latest dates

[hillard]

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.

[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.

 

[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! 

[morr-co]

Hi @hillard - if you know the day ranges you need, 1, 5, and 20 days in this case, I don't think you need the macro. I've added another set of Multi-Row formulas to the workflow to illustrate how to calculate the day 3 values. You will need a set of these tools for each date range of interest. You could modify the 3 day tools to be 5 days (there just wasn't five days of data in the file). 

[hillard · Accepted solution]

Perfect, thank u so much