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. RecordIDCost CentreNameStatusTeamRevenue CCValueAdjustmentTrue UpBillingMarkupTotal Billing2CC1ADelivery IncludedCarenRevenue CC1-1012.1300-1012.13-151.8195-1163.94953CC1ADelivery IncludedCarenRevenue CC11012.13001012.13151.81951163.94954CC1ADelivery IncludedCarenRevenue CC11236.44001236.44185.4661421.9065CC1BSite LegalIncludedMalfoyRevenue CC22927830029278343917.45336700.456CC1BSite LegalIncludedMalfoyRevenue CC2-733503.2100-733503.21-110025.4815-843528.69157CC1BSite LegalIncludedMalfoyRevenue CC2879232.2100879232.21131884.83151011117.0428CC1BSite LegalIncludedMalfoyRevenue CC223820.10023820.13573.01527393.1159CC1BSite LegalIncludedMalfoyRevenue CC27443.78007443.781116.5678560.34710CC1CSite LegalIncludedRonRevenue CC311165.670011165.671674.850512840.520511CC1CSite LegalIncludedRonRevenue CC32709940027099440649.1311643.112CC1CSite LegalIncludedRonRevenue CC31171130011711317566.95134679.9513CC1CSite LegalIncludedRonRevenue CC372.090072.0910.813582.903514CC1CSite LegalIncludedRonRevenue CC3245.9300245.9336.8895282.819515CC1DSite LegalIncludedHarryRevenue CC411263.820011263.821689.57312953.39316CC1DSite LegalIncludedHarryRevenue CC421399.290021399.293209.893524609.183517CC1DSite LegalIncludedHarryRevenue CC43388400338845082.638966.618CC1DSite LegalIncludedHarryRevenue CC41463.92001463.92219.5881683.508

Group by and total - Struggling for 4 days

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

Developer

Developer Tools

Designer Cloud

Data Investigation

Documentation

Accepted answers

Kallis

Friends,

I am truly humbled by the responses. I am grateful for all the solutions that I have received. I am sorry I have not explained the problem much more clearly.

However, I have found a solution. It involves running another process based on the output of the 1st process. There is no way to accomplish this task within the original WF itself.

Thanks @Abhi @ChrisTX @ShivamModi @Qiu @apathetichell

All comments

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.

Screenshot 2024-09-12 142117.png

Chris

Group by and total - Struggling for 4 days.yxmd

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.

0913-Bobjik.yxmd

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

apathetichell

@Kallis - this isn't helping - let's start over. do you want to keep formulas in your excel file - if so you should read up on overwriting specific ranges. you would overwrite the ranges that feed into a specific excel sheet - BUT NOT THE FORMULAS THEMSELVES.

Do you want to do this for a new excel file. You can't - unless you create a blob template - and then follow that step above.

Do you want users to enter values which update formulas -> and then Alteryx sums them -> if so - that's an app.

Otherwise - I have no idea what you want. Perhaps post AN EXCEL FILE. with a sheet of what you have -> and what you want. you've now confused me (which is easy), @ChrisTX and @Qiu - so perhaps take a step back and tell us what you have. what you want to keep. how the user is going to use it. what you want alteryx to do - and how you want it look?

Qiu

@apathetichell
nice comment.

Kallis

Thanks @Qiu @apathetichell @ChrisTX

Thanks for your patience. I am thinking about an alternate method to solve this problem. If that does not succeed, I will inform the user that I will give them the raw data and they will have to create a Pivot to group the data.

ShivamModi

As far as Icouls understand the issue, just add summarise with specifications (SS Attached), it should work. Experts! please comment on this solution, if you think should be done any different and why. This is my first attempt and trying to learn.

Screenshot 2024-09-15 134931.png

ChrisTX

I "think" the request is:

Read the actual formulas from the Excel XLSX file.
- this would require reading the XML from the XLSX file, to get the formula. I've tried it, pretty sure it won't work. Because Microsoft does not store *all* formulas in a way that can be read from the XML.
Dynamically adjust the Alteryx calculation(s) to implement the formula as read from Excel.
- If the original formula was A1 + B1, then a user changed the formula to A1 + B1 + C1, Alteryx would use the new formula to calculate a new result.

AbhishekRathi147

@Kallis I think this is what you are looking for

PS: The table you shared on last comment the entry in Value field for CC1A is wrong, it should be 1236.44 instead of -1012.13.

Hope this solution helps.

Screenshot 2024-09-17 at 8.52.10 AM.png

Bob_Community.yxmd

Kallis

Thanks @Abhi

This is not the solution I am searching for. I tried this before I turned to the community. The main problem here is to Sum the number columns and retain the formula at the backend.

Anyway, thanks for your support

Kallis

Friends,

I am truly humbled by the responses. I am grateful for all the solutions that I have received. I am sorry I have not explained the problem much more clearly.

However, I have found a solution. It involves running another process based on the output of the 1st process. There is no way to accomplish this task within the original WF itself.

Thanks @Abhi @ChrisTX @ShivamModi @Qiu @apathetichell

Qiu

@ChrisTX
Nice to hear that you have found a solution.

I do hope the disucssion here helps in the process.

Kallis

@Qiu

Yes sir. Absolutely. The discussions here gave me a lot of insights.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15881

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703