Group by and total - Struggling for 4 days

[Kallis]

Hello Friends

I am struggling with a requirement for the past 4 days.I have given a table below. 

The requirement is that this must be GROUPED BY Cost Centre. 

One Cost Centre should have one line which means that the columns from Value till Total Billing must be summed up. The problem is that some of these columns, Billing till Total Billing are STRINGS. I have replicated the Excel formula behind these columns. 

Please see the attached screenshot from the Alteryx Output window.

If I convert these columns into double or int so that I can use the Summarise tool to Group By, the formula is gone and the columns become empty. I tried the Formula tool to convert these columns into a number but the result was the same. 

I hope I have explained somewhat clearly. Kindly help.

RecordID	Cost Centre	Name	Status	Team	Revenue CC	Value	Adjustment	True Up	Billing	Markup	Total Billing
2	CC1A	Delivery	Included	Caren	Revenue CC1	-1012.13	0	0	-1012.13	-151.8195	-1163.9495
3	CC1A	Delivery	Included	Caren	Revenue CC1	1012.13	0	0	1012.13	151.8195	1163.9495
4	CC1A	Delivery	Included	Caren	Revenue CC1	1236.44	0	0	1236.44	185.466	1421.906
5	CC1B	Site Legal	Included	Malfoy	Revenue CC2	292783	0	0	292783	43917.45	336700.45
6	CC1B	Site Legal	Included	Malfoy	Revenue CC2	-733503.21	0	0	-733503.21	-110025.4815	-843528.6915
7	CC1B	Site Legal	Included	Malfoy	Revenue CC2	879232.21	0	0	879232.21	131884.8315	1011117.042
8	CC1B	Site Legal	Included	Malfoy	Revenue CC2	23820.1	0	0	23820.1	3573.015	27393.115
9	CC1B	Site Legal	Included	Malfoy	Revenue CC2	7443.78	0	0	7443.78	1116.567	8560.347
10	CC1C	Site Legal	Included	Ron	Revenue CC3	11165.67	0	0	11165.67	1674.8505	12840.5205
11	CC1C	Site Legal	Included	Ron	Revenue CC3	270994	0	0	270994	40649.1	311643.1
12	CC1C	Site Legal	Included	Ron	Revenue CC3	117113	0	0	117113	17566.95	134679.95
13	CC1C	Site Legal	Included	Ron	Revenue CC3	72.09	0	0	72.09	10.8135	82.9035
14	CC1C	Site Legal	Included	Ron	Revenue CC3	245.93	0	0	245.93	36.8895	282.8195
15	CC1D	Site Legal	Included	Harry	Revenue CC4	11263.82	0	0	11263.82	1689.573	12953.393
16	CC1D	Site Legal	Included	Harry	Revenue CC4	21399.29	0	0	21399.29	3209.8935	24609.1835
17	CC1D	Site Legal	Included	Harry	Revenue CC4	33884	0	0	33884	5082.6	38966.6
18	CC1D	Site Legal	Included	Harry	Revenue CC4	1463.92	0	0	1463.92	219.588	1683.508

[apathetichell]

As I read this - you want to keep the values as formulas--- AND get the values out of the formulas for other parts of the workflow.

my recommendation would be to bring in the file twice via batch macro - one time to get the formulas - one time to get the numbers. I'd add a recordid in my batch macro so I knew which row corresponded to which. I'd probably create a tag via however I was changing my input data - and then use a filter tool to pass it to the correct output. I'd configure my interface designer/macro to union by name. In my main workflow I'd join my two outputs by record id. I can do this after or before manipulating my numeric side to group by/whatever.

You can try posting your workflow/real data/a .yxzp.

[ChrisTX]

Do you just need to change some of the data types from String to Numeric, then summarize by Cost Centre?

See attached workflow.  I used the Select tool to change two fields to a String, then used a Multi-Field Formula tool to convert string fields to numeric.

 

Chris

[Kallis]

Hi @ChrisTX 

Perhaps, I wasn't clear with the requirement.

The formula in the Billing, Markup & Total Billing must be retained because the changes in the columns, Value, Adjustment & True UP will affect the Billing, Markup & Total Billing columns. 

I want to Group by Cost Centre and also summarise the numbers.

[Kallis]

Hi @apathetichell 

I have never used macro before. Is there any other method?

Regards

Bobji

[Qiu]

@Kallis 
I dont quite get this part "The problem is that some of these columns, Billing till Total Billing are STRINGS. I have replicated the Excel formula behind these columns. "
If some value turns to empty, it means that it contains some non-numeric characters.
Anyway, I made a simple sample flow to calculate the columns  "Billing", "Markup" & "Total Billing", then get the sum up for each cost center.

 

[apathetichell]

@Qiu - I think you parsed the request correctly - I read it as being much more complicated (ie keeping the raw formulas from excel for lineage - and manipulaing the values).

[Kallis]

Hi @Qiu 

Thanks for your response but unfortunately, this wasn't the solution I was looking for. 

The columns, Adjustment & True Up must be available in the final excel for the user to add any value. This must be reflected in the Billing, Total Billing and the Markup columns.

[Qiu]

@Kallis 
Can you share some sample input and desired output so we can take a closer look?

Only wording it is difficult to replicate the issue.

[Kallis]

Hi @Qiu 

The input is as given in the beginning of this thread.

This is the expected output.

One line per cost centre with the columns Value, Billing, Markup, Total Billing summed up.

For example, 3 rows with Cost Centre as CC1A is summed up in the first row. Likewise, 5 rows for CC1B in the second row, so on and so forth.

Hopefully, I have made it clear.

Also, this is the formula

Billing - =sum(Value + Adjustment + True UP)

Markup - =Billing * 15%

Total Billing - =sum(Billing + Markup)

These formulas must stay so that when the user adds any value to Adjustment or True Up, it will be reflected in the above columns

Cost Centre	Name	Status	Team	Revenue CC	Value	Adjustment	True Up	Billing	Markup	Total Billing
CC1A	Delivery	Included	Caren	Revenue CC1	-1012.13	0	0	1236.44	185.466	1421.906
CC1B	Site Legal	Included	Malfoy	Revenue CC2	4697775.88	0	0	469775.88	70466.382	540242.26
CC1C	Site Legal	Included	Ron	Revenue CC3	399590.69	0	0	399590.69	59938.604	459529.29
CC1D	Site Legal	Included	Harry	Revenue CC4	68011.23	0	0	68011.03	10201.655	78212.685