How to test row count on iterative dataset

[sheidari]

I have a dataset where i'm tracking adjustments made to sales. each adjustment is treated separately and grouped by ID. you can have multiple adjusments for the same ID but they must be treated separately and not aggregated.

I'm trying to get a row count based on the adjustment date. So if the adjustment date is 3/1/2021 and the last sales transaction was recorded on 3/1/2020 ( 1 year ago) then that would be my starting point, up until the earliest date for that ID. an example is below. The example only shows on ID but this would be applied across thousands of IDs so it will need to be grouped by ID. Append Tools won't work because my dataset grows exponentially and my workflow doesn't seem to finish running.

[danilang · Accepted solution]

Hi @sheidari 

Here's how you can do it

Split the data into adjustment rows and sales rows. Add an AdjustmentID for sorting at the end and then join on ID.  Add a filter to pass only the rows where sales month <= AdjustmentMonth and then group by ID and AdjustmentDate counting the sales.

The counts are off because I didn't type in the entire mass of data that you had in your image.  I did however, add a second ID

to validate the concept.

In future, please attach a file if you have a large amount of data, as opposed to an image.  

Dan

[sheidari]

Thanks, @danilang!

This is exactly what I needed and worked perfectly with my data. 

Next time, I'll provide the data in a file if it's too big, thanks for the tip.