How to compare data day over day

[Taxautomation]

Hi, I have a workflow that runs daily. I'd like to implement something that allows me to see the change in data (numbers) day over day.

For example, I run the workflow on day 1 and get this data.

Acct	Amount
xyz-123	10

When I run the report for day 2, I want the previous data and the change.

Acct	Amount	Amount Prior Dat	Change
xyz-123	13	10	3

I can get this to work, but I have to connect inputs to the workflow every time I run it for the prior day file. I want Alteryx to recognize the prior day data dynamically without have to connect it to the prior day output.

[CoG]

Where is prior day data being stored? If possible, the simplest solution may be to output data to a .yxdb which stores a second copy of the workflow output, but titled something generic, like "Prior Data". This will be overwritten every day and can also be used as a fixed reference input to join and perform the comparison against.

Hope this helps and Happy Solving!

[Hub119]

Alternatively to @CoG 's solution, if you are logging your old data and need to keep what was produced each day (based on your description, I'm assuming in separate files?) you could also use a directory input tool to query available files, sort to the most recent one (probably based upon CreationTime?), filter (i.e sort and sample) to only that file/record and use the Dynamic input tool to pull in only your most recent (last day's) file each time the workflow is run.

[Taxautomation]

As of right now, the workflow outputs data for comparing to the next day, and I am picking up the most recent file on that folder, then wouldn't the workflow compare the same data? 

How do I tell Alteryx to compare it to yesterday's report dynamically? Is there a formula I can use that takes "CreationTime" and subtracts one business day? Then I'll only need to have the dynamic input pick that up.

Kinda seems to work if I only run the report once

[Hub119]

@Taxautomation one way of doing this would be to leverage the function DateTimeToday() to grab today's date when running the workflow, and then use the DateTimeAdd function to subtract one day "DateTimeAdd(DateTimeToday(),-1,"days")" and match that up with the date of file creation parsed from your file creation time (the CreationTime field) that comes in from the Directory tool.  If only a single file is created and written to the folder in question each day, filtering on that date match would allow you to narrow down to the singular file you wish to pull in, and then use the FullPath of that record to update the Dynamic Input tool.

Hopefully that makes sense.

[TUSHAR050392 · Accepted solution]

Hey @Taxautomation, Another approach - I will use two input streams, one which reads current day data and other which reads previous day output. If you have multiple accounts, you can join on accounts and then compare the values. That will be easiest way to compare current and previous day data. Hope this helps