Entering Subtotal lines in existing data

[andrewplaice]

Hi all,

I have read a number of postings, but can't find this exact scenario or I am simply too thick to recognize it!  I'm a newbie so go easy on me, but something that I feel should be easy  I just can't do... I'm going to the experts. 

I am trying to automate a report for one of my account managers, who creates this report for their client.  I am trying to get the output as close to his format as it is today... can certainly be flexible.  

May data looks like this:

Account Number	Name	Date	Income
12345	Jennings	01/15/2020	-41,554.91
12345	Jennings	03/19/2020	-43,490.78
34567	Walker	01/15/2020	-562.64
34567	Walker	03/19/2020	-501.89
98765	Adams	01/15/2020	-5,211.59
98765	Adams	02/13/2020	-19,373.03

I need my output to look like this:

Account Number	Account Name	Entry Date	Income
12345	Jennings	01/15/2020	-41,554.91
12345	Jennings	02/13/2020	-153,591.66
12345 Total			-195,146.57
34567	Walker	01/15/2020	-562.64
34567	Walker	03/19/2020	-501.89
34567 Total			-1,064.53
98765	Adams	01/15/2020	-5,211.59
98765	Adams	02/13/2020	-19,373.03
98765 Total			-24,584.62

I appreciate the help!!

Andy

[MarqueeCrew]

From my iPhone I have a suggestion for you @andrewplaice :

put a record I'd in the incoming data

SUMMARIZE:

Group by Account Number

Sum Income

SELECT:

 make sure account number is vstring 

FORMULA: [Account Number] 

[Account Number] + " Total"

UNION:

original data + data from this new process

SORT:

account number + recordid

select to remove recordID 

cheers,

 mark

[JosephSerpis · Accepted solution]

Hi @andrewplaice I mocked up a workflow let me know what you think?

[andrewplaice]

Wow.  You built that faster than I was able to decipher what you did!  It is exactly what I need.  Thank you.  

For my own learning, can help me understand what this formula is saying?

tonumber(Replace([Income], ",", ""))

[JosephSerpis]

Hi @andrewplaice that formula is replacing the , in your income with nothing as alteyrx is reading your income field as a string due , being present and then converting to a numeric field in order to be used in the summarize tool to sum up the income for the total.