Hello Everyone,
I posted about this but didn't get a response back. So, trying again. Hoping to hear back this time around.
Sample Data:
Note: The actual data set is over 100,000 rows and contains a lot more fields, but for the sake of getting help, I've simplified it down to the main fields that I need to work through in the dataset. I've reworked the data here to get the point across.
^ I've calculated the Initial Denied Amount manually and set it up how I'd like to see it in the output.
I need to be able to calculate the Initial Denied Amount for a given ID #, based on the first/oldest Check Date by summing Denied Amount for a respective ID # for only the first date/oldest date. An ID # can have multiple records/rows for multiple Check Dates, some the same date and some different. I want to automate the calculation using Alteryx because I cannot do this for over 100,000 ID's every week.
Example:
For ID 6354, I want the Initial Denied Amount to equal $4,517.97. I summed up the Denied Amount for the earliest Check Dates for the ID: 1/17/2020 (NOT include 1/20/2020 - as it's not the first time stamp the ID was shown). To sum: $3,388.07 + $936.70 + $193.20 = $4,517.97. And then the Initial Denied Amount column to reflect the total summed value for the ID.
I know this can be done through some summarizing and conditional formulas in Alteryx. But, I am stuck, I would really appreciate the help getting this logic figured out.
Best,
Maneet
