Creating a Pivot Table with dynamic filters connected to UI

[GSWill]

Hi Community Members,

I was wondering if it is possible (and efficient) to create something similar to a Excel pivot table in Alteryx where a user can select which data fields (and how many) become rows, stay as fields, or are set as filters or types of aggregates. I am assuming this would involve some interface tools to alter a complex map of summarize and cross tab tools - however, with over 20 fields that leadership may want to analyze, it's seeming like a very time-consuming process.

The main advantage would be to create a more friendly interaction interface between the user and a large of amount of complex data than Excel itself.

A yes or no response and reason why is good enough. Thank you for your input!

[KilianL · Accepted solution]

Hi @GSWill ,

for a "pivot table" in Alteryx it is usually enough to use the Transpose and Cross-Tab Tools.

Do I understand you correctly that you want to create an Alteryx App to offer this capability? So instead of doing it in Designer, leadership can do it via the App?

There are ways to create a dynamic App as such. I did it before as a small example for a reporting app, where you can choose which fields you want to display in a chart. It might work in a similar way for your pivot table.

For this you will need 'chained' Apps - one to create the dropdown for the field selection (in case they differ from Input to Input) and one to generate the pivot. The idea is to dynamically rename the selected fields to e.g. 'new header' or 'new value' so the Cross-Tab tool can pick it up.

You can find this example attached.

I am not sure how complex it will be to do this for your use case, but this can give you an idea.

Please mark this as the solution if it answers your question, it will help others to find solutions quicker.

Kind Regards,
Kilian
Solutions Engineer - Alteryx

[DawnDuong]

Hi @GSWill 

@KilianL already provides you with a possible Alteryx implementation 

On whether it is efficient to do that — my opinion is no. What you describes is probably a use case for data visualization tool (powerBi, Tableau, Spotfire etc...) which can let user dynamically select dimensions and measurements to report.

dawn 

[GSWill]

Thank you Kilian - your solution is what I imagined the workflow to look like. My dataset is pretty complex but this gave me a direction in case I need to build it out. Have a great week!

[GSWill]

Thank you Dawn! I believe you are 100% correct and in this case it is probably easier to use a data visualization tool. Appreciate it!