Calculate Sub Totals and Output separate excel files grouped by country

[schoeneborn-hendrik]

Hey all, 

I am looking for any support on a workflow calculating sub totals and outputting separate excel files by country.

My input is an excel file which is supposed to be separated by country. In addition, the output excel file is supposed to contain sub totals in the last row. I hope this image illustrates my workflow I am looking for:

Approach:

1. Calculate sub totals for each country

2. Output separate excel files for each country expanded by a final row containing the sub totals

Problem:

- Calculation of sub totals works fine BUT finally it generates two separate files for each country. 1st file contains dataset (e.g. "China") and the 2nd file only contains the sub totals (e.g. "China Total").

I know that this problem arises because my title for the sub totals are called e.g. "China Total" and listed in the column "Country". So, Alteryx recognizes "China" and "China Total" as different countries and generates separate output excel files.

Actually, the files "China" and "China Total" are supposed to be in one file.

Please find my workflow and a sample file in the attachment.

I am thankful for any support!

[grazitti_sapna]

Hi @schoeneborn-hendrik , I am not sure if this is a best approach but as per your requirement I have made a minor change if it is feasible for you. Please refer to the screenshot.

In this screenshot, I have replaced your forumla tool where it was hardcoding the country with total eg: China Total due to which it was being considered into a separate file. Now I have changed the logic to

if IsNull([Business Sponsor]) then [Country]+"Total" else [Business Sponsor] endif

and used it after the first union tool in order to achieve the desired output.

Also attaching the workflow along with this post for better understanding.

I hope this helps.

Thanks.

[schoeneborn-hendrik]

Hi @grazitti_sapna ,

thanks for your immediate response.

Since you have a more recent version of Alteryx, I am not able to open the workflow. My version is 2019.3.5. Are you able to share it in a compatible way for me?

Thank you a lot in advance!

[grazitti_sapna]

Hi @schoeneborn-hendrik , try this if it works for you?

If you still face an issue right click on the workflow file then edit it in the notepad. Change the version to the one you are using as shown in the image.

Thanks.

Note: please change the input source. Also your yxzp file might be extracted then you can just edit yxmd file by opening the notepad as instructed above.

[schoeneborn-hendrik]

Hi @grazitti_sapna ,

thank you very much! I am able to open the workflow and it is exactly doing what I was looking for!

One last question: I would like to highlight the "Total" row. Is it possible to format the Total row e.g. in bold font using my current workflow?

[grazitti_sapna]

Hi @schoeneborn-hendrik , yes it possible you need to change the configuration of table tool.

1)Click on Business Sponser then edit row rule.

2) A row styling rules confi box will open up where you can make this configuration as shown in the image below.

3)Final Output, I hope this what you are looking for?

Attaching the workflow along in case you face any difficulty.

If this post helps you please mark it as solution.

Thanks.

[schoeneborn-hendrik]

Hi @grazitti_sapna,

perfect, thanks a lot!

My final questions:

1.) Is it possible to remove the country in the total row? (see red frame)

2.) Is it possible, to rename "China Total" into "Total"?

3.) In the total row: Is it possible, to calculate and show the share of totals in columns 4, 5 and 6 of the total in column 3 in % in brackets behind the totals (option "green") or below the totals (option "blue")? Is formatting possible? (image just for illustration)

Thank you a lot in advance!

[grazitti_sapna]

Hi @schoeneborn-hendrik , yes that is possible too.

1) Modify the formula from  if IsNull([Business Sponsor]) then Country+"Total" else [Business Sponsor] endif

to if IsNull([Business Sponsor]) then "Total" else [Business Sponsor] endif

2) Add a column rule to country as given below.

3) Final Output.

Attaching the workflow along for your understanding.

I hope this works for you.

Thanks.

[schoeneborn-hendrik]

Hi @grazitti_sapna ,

thanks, works perfectly fine!

Finally, In the total row: Is it possible, to calculate and show the share of totals in columns 4, 5 and 6 of the total in column 3 in % in brackets behind the totals (option "green") or below the totals (option "blue")? (image prepared in ppt for illustration)

Thank you for your support!!

[grazitti_sapna · Accepted solution]

@schoeneborn-hendrik , is this what you are looking for?

Thanks.