Getting a Total Sum Based on Conditions (Formula help - Beginner at Alteryx)

[Maneet_Deol]

Hello Everyone, 

I am trying to crunch a data set to get me an initial denied amount ($) for a unique ID based on the earliest date range for the respective account. 

An account can have multiple records for a particular date and other dates following after. Each unique ID may have multiple rows of records basically for a number of different dates and a denied amount associated with each record. 

Essentially: I am trying to sum the initial denied amount based on the earliest date the row is recorded. If the ID has multiple records for the same date, I want to sum the total denied amounts for all earliest dated records. 

Sample Data:

Note: This is not the actual data but I've created a dummy set to help get my question answered. The actual data is over 100,000+ rows and many more fields that are not necessary for this calculation. 

The Initial Denied Amount is calculated manually right now to show you how I'd like to view the data once it is calculated.

For example: For ID: 6362, I want the Initial Denied Amount (Column D) to sum only the Denied Amount (Column C) for the earliest/oldest dates for the respective ID, 6354. In this case sum the Denied Amounts for the records on Check Date 1/17/2020: $3,388.07 + $936.70 + $193.20 = $4,517.97 as the Initial Denied Amount for all rows for ID 6354. 

In excel, we can calculate that sum value through pivot tables and such, but that is very manual and time-consuming, as I need to calculate these numbers every week and for over 100,000+ records at a time. I have already gotten halfway to clean the data to this point.

The methods I've thought of were doing some concatenating, then using the summarize tool, followed by joining back to original source. But, I feel stuck at this point 😕 

I don't know what formula to use or how to go about this to automated the workflow. 

I appreciate the help. The sample data is attached. 

[AbhilashR · Accepted solution]

@Maneet_Deol - just saw this post of yours.. can you mark this closed since we have already addressed the requirement in another post?