Estimating date of purchase of sold products in Alteryx

[TKV]

Hi everyone,

I am a retail sales analyst and I am trying to build a model that approximates the month of purchase for a given sales month, in order to know "how long ago did I buy what I just sold". I have COGS, purchasing, and ingoing / outgoing inventory data, but I don't have access to inventory data that shows which inventory I sold in which month.

I am looking for help with automating this calculation process in alteryx and identifying / estimating the main month of purchase for each sales month. It is fair to assume the FIFO principle, so at any given sales month I would sell out what I purchased first (and is still available) If anyone has experience with this or has any suggestions, I would greatly appreciate your input.

I have attached a sample of my data (grey cells), and how I would solve it for a small dataset in excel (white cells). The output showing for example that what I purchased in April I sold 40 of in May and 60 of in June. My real dataset is significantly larger (millions of rows).

My hypothesis is that we might be able to solve this with some sort of running totals, but I just can't seem to crack it. 

Thank you in advance for your help and advice.

Best,

[ShankerV]

Hi @TKV 

I think this will be more interesting to work.

However before I need to understand the underlying data set better, so the expected outcome can be derived.

Many thanks

Shanker V

[TKV]

Hi @ShankerV,

Thanks for your swift reply. Please find attached a longer example. Due to data confidentiality I cannot send you the entire dataset, but the attached file is a good representation in small scale.

The dataset contains a column with a Product name that is unique for that product and a date (month-year combination). The other columns represent the inbound and outbound value of inventory at that time for the product and the COGS-sales at that period. The purchase in any given month equals the change in inventory during the month + the COGS-sales. I have hardpunched what the output would look like for one product, but as you can see I have not automated it in excel either.

Thanks a lot!

TKV 

[ShankerV]

@TKV 

Thanks for the sharing the sample dataset. 

That helps!!!

Will work on it and get back to you shortly.

Many thanks

Shanker V