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]

@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.

[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.

[Abhi]

@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.

[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.