How do I create a formula for Daily Variance, given that the Colum headers / name changes every day/
(This data set will have a continuous cycle of 3 dates, with the oldest being removed, and the latest being added)
@Dominic_Albans you can can calculate the variance separate and append with the final output
transpose - order your dates/previous column headers. change create values called day/day-1/day-2 in formula tool. use dynamic rename to match the [Name] column with your original date names to your original data stream - rename to day/day-1/day-2. use formula tool to perform calculations on static names (ie day/day-1/day-2) - if needed - remap original column names.
@Dominic_Albans when you say column name changes do you mean the columns given in the above workflow in the text input change? Can you provide a sample input file for better understanding?
The Colum name is derived from a formula "[Name]+':'+[Date]". As new data, for a new date, is expected everyday; how can I create a dynamic formula which calculate the variance between the last 2 date values.
Input File:
@Dominic_Albans The given formula is dynamic, when you say variance are you expecting a new column called variance? can you provide the expected output?
Sure:
Note the new column "Daily Variance: Cost" on the far right. Which is Cost:13/08/2024 - Cost:12/08/2024.
I need the formula and resultant new Colum, to dynamically account for changes in the Header due to a new date. For example, tommorow it should be: Cost:14/08/2024 - Cost:13/08/2024.
are you calculating the variance only for the last two dates?
Yes
