Compare two different snapshots of data within the same dataset

Question

I have a large dataset that grows each week.  It is a table of weekly snapshots of the same columns.

Example, each Monday, it shows all the customer orders for the next year in the future. (a rolling forecast)

I need to create a workflow that can ingest this singular dataset, compare the week to week variance over time, and be able to calculate a week to week % that is a table output that could be made into a simple moving average trend.

I'm sure this is possible with Alteryx but I don't know where to start. Any ideas?  I can provide a mock dataset if that would help.

SeanAdams · Answer

Hey @craigedelman ,

I did a post a while back about how to compare 2 data sets - this was geared towards the recon requirement (what's different) but the pattern should be the same.

Compare 2 Data Sets - Alteryx Community

In your case - you could just bring in last week and this week and do a comparison by joining them together with a shared key.     There will be complexities if you have items that do / do not appear week over week (and you can deal with that by using all 3 legs of the join).    If all you want is a delta on a single field - then a join is much easier; followed by a formula tool - if you want a delta on ALL fields then you'll need to do something more like the document above.

The starting point is to make sure you have a shared key across the two sets.

Have a read of the document and hopefully that helps you to find a solution that works.

craigedelman · Answer

the new forecasts are appended on the existing dataset.  the new appended dataset has the snapshot date and time.

FrederikE · Answer

Hey @craigedelman,

Since you are talking about forecasts, i expect the old forecast should be overwritten in the newest file?

In this case you could write two output files. One containing actual data and a second one that is just used for reference.

This reference file is then being pulled in every week, metrics are calculated and you overwrite the reference file again, with newer data (if the reference changes weekly as well, if it doesn't you need to imply a logic that overwrites only when a certain condition is met).

Hope this is helpful. If not, can you share a dummy data file?