Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Summarizing Calculation/Conditional Sum with Alteryx workflow

Maneet_Deol
7 - Meteor

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.

 2020-03-25 09_46_25-HealthRise - Desktop Viewer.png

 

^ 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

8 REPLIES 8
AbhilashR
15 - Aurora
15 - Aurora

@Maneet_Deol - you will need to use a summarize tool to sum up all denied amounts for a given ID and check date combination. Join it back to the original dataset, and then write a multi-row formula to duplicate the Denied Amount for every ID.

Capture.PNG

Hope this helps! Please let us know if you have more questions, or mark this post as solution accepted if it does.

Maneet_Deol
7 - Meteor

Hey @AbhilashR - Can you share the workflow in a format for an older version of Alteryx? I can't open the packaged file in my version. 

AbhilashR
15 - Aurora
15 - Aurora

@Maneet_Deol - attached file has been downgraded to open in version 2018.2 and above. Hope this helps.

Maneet_Deol
7 - Meteor

Thank you @AbhilashR - I think your solution will work. I need to run it on the actual data set to confirm. I will surely let you know before the end of the day. 

 

But, would you mind sharing your logic around the "counter" formula you used and the Running Total tool? I'd like to understand the solution better for my own problem-solving learning. 

 

Please and thank you! Mad helpful.

AbhilashR
15 - Aurora
15 - Aurora

@Maneet_Deol - am glad this solution worked out for you!

 

I created the counter field to give my Running Total tool and base value to work with.

The Running_Tool_counter field itself was created to identify ID's that have multiple CheckDates

I use the value in Running_Tool_counter to duplicate Tot_Denied_Amount for ID entries which have multiple (&duplicate) CheckDates.

 

Let me know if this helps, and please mark this post as solution accepted if it answered your questions.

Maneet_Deol
7 - Meteor

@AbhilashR - Your solution worked perfectly! Thank you so much!

Maneet_Deol
7 - Meteor

@AbhilashR Would you willing to help with hierarchy based matching related to the data you helped with?

AbhilashR
15 - Aurora
15 - Aurora

@Maneet_Deol - sure. Feel free to create a new post and tag me to look at, or post it here. Whatever works for you,

Labels