topic Re: Nested Output Using Averages But Averaging Totaling on List in Alteryx Designer Desktop Discussions

Nested Output Using Averages But Averaging Totaling on List

knobsdog — Thu, 28 Jan 2021 20:47:52 GMT

I have data in row/column format that's nested. I need to get an Average Task Duration of each Group, Channel and Task Segment. I've tried several different separate and join options in workflows but every single one brings them back nested but the Group level is just summing or averaging each shown value. For example if I had Group 1 Channels 1,2,3 with Task Segments A,B,C the group level is showing 3 instead of 1 whether I pick average or sum using Summarize or Crosstab tools.

What I Have

GroupChannelTask SegmentAvg_Task Duration

1	Red	Circle	3.42
1	Red	Square	12.03
1	Red	Triangle	1.64
1	Red	Rectangle	1.57
1	Blue	Circle	3.27
1	Blue	Square	7
1	Blue	Triangle	2.28
1	Blue	Rectangle	2.66
1	Green	Circle	1
1	Green	Square	1
1	Green	Triangle	0.01
2	Yellow	Circle	5.18
2	Yellow	Square	13.97
2	Yellow	Triangle	3
2	Yellow	Rectangle	1.86
2	Orange	Circle	3.43
2	Orange	Square	10.89
2	Orange	Triangle	1.93
2	Orange	Rectangle	3.05

WHAT I NEED:

	Circle	Rectangle	Square	Triangle	Grand Total
1	2.56	2.12	6.68	1.31	3.26
Blue	3.27	2.66	7.00	2.28	3.80
Green	1.00	0.00	1.00	0.01	0.67
Red	3.42	1.57	12.03	1.64	4.67
2	4.31	2.46	12.43	2.47	5.41
Orange	3.43	3.05	10.89	1.93	4.83
Yellow	5.18	1.86	13.97	3.00	6.00
Grand Total	3.26	2.29	8.98	1.77	4.17

Re: Nested Output Using Averages But Averaging Totaling on List

JagdeeshN — Thu, 28 Jan 2021 21:07:49 GMT

Hi @knobsdog ,

Please find attached a sample workflow which does the transformation you were looking for.

Do let me know if this works.

Best,

Jagdeesh Narayanan

Re: Nested Output Using Averages But Averaging Totaling on List

knobsdog — Thu, 28 Jan 2021 22:17:31 GMT

I'll take a look, thank you for your quick response.

Re: Nested Output Using Averages But Averaging Totaling on List

knobsdog — Fri, 29 Jan 2021 13:46:18 GMT

I had to adjust a couple things but your solution got me 95% there. So thankful for your help, I really appreciate it.

Re: Nested Output Using Averages But Averaging Totaling on List

JagdeeshN — Fri, 29 Jan 2021 13:47:03 GMT

@knobsdog

Awesome!. Glad I could help.

Re: Nested Output Using Averages But Averaging Totaling on List

knobsdog — Fri, 29 Jan 2021 14:39:29 GMT

Thanks again.

Re: Nested Output Using Averages But Averaging Totaling on List

JagdeeshN — Fri, 29 Jan 2021 14:39:54 GMT

@knobsdog

Please check if the solution in the below thread helps your usecase:-

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Bolding-specific-text/td-p/334281

Best,

Jagdeesh Narayanan

Re: Nested Output Using Averages But Averaging Totaling on List

knobsdog — Fri, 29 Jan 2021 14:41:25 GMT

I spoke too soon. I figured out how to write a formula in the Basic Table tool that will bold and increase the font size based on the text in the column.

Thank you again for your help.

Re: Nested Output Using Averages But Averaging Totaling on List

JagdeeshN — Fri, 29 Jan 2021 14:43:33 GMT

@knobsdog

Awesome. Can you share a sample formula here if you don't mind?.

I ask as that can be helpful to others.

Best,

Jagdeesh Narayanan

Re: Nested Output Using Averages But Averaging Totaling on List

knobsdog — Fri, 29 Jan 2021 15:06:57 GMT

Good idea here's the steps:

In the Basic Table Tool:

Under Per Column Configuration Section at the bottom

1. Select field you want to adjust the font,
2. Click Column Rules > Create
3. Check Formula button
4. Click 3 dot menu and type formula: [_CurrentField_] IN ("1","2"..."n")
5. Below Formula box check Font box and pick bold, italic, font, etc
6. Check Font Size and pick font size
7. Click Close button