Calculate Sum, Count & Average of all field without using Summarize tool

Hi All,

I have a dataset which has close to 800 columns. I want to calculate the Sum, Count & Average of each field. I'm able to do it using Summarize tool but the problem is that the dataset can have additional fields or previous fields might get removed in future. So in this scenario the Summarize tool fails since it gives error for missing fields.

Transposing the entire data and then using Summarize is an option but since there are 800 fields so the volume of data is huge and transposing the data takes lot of time(close to 15min to execute). Not sure how to go around this. Appreciate any help around this.

Thanks in advance.

Common Use Cases

Accepted answers

binu_acs

@hemant86 use dynamic summarise for your use case

https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Dynamic-Summarize/td-p/64335

sample workflow attached

Dynamic_Summarize_18022025.yxzp

All comments

binu_acs

@hemant86 use dynamic summarise for your use case

https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Dynamic-Summarize/td-p/64335

sample workflow attached

Dynamic_Summarize_18022025.yxzp

Qiu

@hemant86
It is to me that the Transpose the data to vertical format then apply Sum/Ave is the good way to go.

About the performance, have you tried to turn on the AMP engine to make it abit faster?

Yoshiro_Fujimori

Hi @hemant86 ,

If you just need the Sum, Count & Mean of all fields, Field Summary Tool may work.

https://help.alteryx.com/current/en/designer/tools/data-investigation/field-summary-tool.html

It provise a variety of statistic information of the input data.

I hope this helps. Good luck.

workflow.yxmd

Qiu

@Yoshiro_Fujimori
I have not used this tool. thank you !

hemant86

Thank @binu_acs This should do the job for me. 😊

hemant86

Hi @binu_acs I was checking the macro and and do not see where you have taken the option to populate Count & AVG. How do I modify the macro to count only NOT Null values.

binu_acs

@hemant86 what you have to do is check the xml part of the summarise tool and how each action given there, then copy the action and paste in the second input text tool

Dynamic_Summarize_18022025.yxzp

hemant86

Thanks @binu_acs I learnt something new today. Now I can use Dynamic Summarize for all kind of calculations.🙂

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703