Finding historical averages

I have a dataset with few years of historical orders and sales. I am trying to come up with a 3 Yr Avg% and 5 Yr Avg % which is essentially 3 Yr Sum (Orders)/3 yr Sum (Sales) and 5 Yr is based on historical 5 Years (For each state and each week) . Only caveat is it has to ignore the latest year available in dataset, e.g. in current example it will ignore 2021 information and goes further 3 and 5 year back. I have attached dataset in the excel with current and expected views.

Trending.xlsx

Common Use Cases

Preparation

Accepted answers

AngelosPachis

Hi @AlterIT ,

Do you mind trying to run the workflow? If you run it, that error should go away and the workflow should run fine

All comments

AngelosPachis

Hi @AlterIT ,

Here's one way you can do it. I've transposed your table so all values for sales and orders extend vertically. Then I've parsed the year from the column headers and found the max year in the dataset.

Then for those that are 3 years back to the max year-1 (max year is excluded) and 5 years back I have estimated the averages of orders to sales and joined back to the master table

Hope that helps,

Angelos

example.yxmd

AlterIT

Thanks, that seems to be it. However I keep seeing this error while I go through the components

AngelosPachis

Hi @AlterIT ,

Do you mind trying to run the workflow? If you run it, that error should go away and the workflow should run fine

AlterIT

Thanks a lot !! with this help

Robert_BlackeyDM

Quick Links

This months top contributors

SophiaF 432

AkimasaKajitani 390

mceleavey 388

mbarone 337

michael_weaver 335