Hi All! New to Alteryx, creating my first workflow based on one I previously developed in another tool, having issues with filtered totals. In short, I create a pivot table and added row & column totals. After creating the pivot table I filtered out certain rows. The filter tool output displays the correct records but the column total includes counts for all of the rows, including those that are filtered out. My only guess as to why this is happening is because the Total row generated during the creation of the pivot table displays a static value (from all of the records) and is not not dynamically summing the rows appearing in the filtered result. Is that correct? To get around this issue I created the initial pivot table without the row/column totals, and then after filtering the results unpivoted the data (using transpose), then re-pivoted it again and added the row/column totals during this second pivot. Appreciate any thoughts or feedback. Thank you!
Hi - I'm confused:
The filter tool output displays the correct records but the column total includes counts for all of the rows, including those that are filtered out.
The filter tool filters out rows - the select tool can deselect columns. The information flows in a line so a pivot (transpose tool?) that comes before a filter tool won't be affected by a filter that comes after it. You may want to use a transpose after it. Assuming we are talking about static vs dynamic in Alteryx (vs files created by Alteryx in excel) - Alteryx is dynamic.
Can you post some of your workflow and data and what you are trying to do?
Hi, thanks for your response. Yes, I'm seeing the correct rows filtered but getting the total for all of the rows in the pivot table. I've attached the data files and workflow. Also attached a picture of workflow - the total of "Sum_Closed" column should equal 12 (based on filtered rows) but it's including adding all of the rows, even those that are hidden. Thanks again for the assistance.
@mmelcer
Maybe @apathetichell has better explanation, but you are right, Alteryx datastream is static not dynamic.
Its like a revier, and the change on downstream can not reflect back to upstream.
In order to get the correct result, we wiill the Transpose and Cross Tab again.
Hi @mmelcer
You would need to apply the total mechanism after filtering like below.
Workflow:
Hope this helps : )
@atcodedog05
Same thinking. 😁
-
Thank you @Qiu and @atcodedog05 for the responses, much appreciated. The revised workflow makes sense.