Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Batch macro. and multiple filters to create ranks and percentiles

chickenlicken
8 - Asteroid

I thought I cracked it with this macro but alas not.  

 

I am trying to create a macro that creates median, quartiles and ranks for different subgroups (I am analyzing a large data file where there are 30+ subgroups for which I need to do these calculations).  

 

The output structure I am looking for would be a single line per department per year with columns for value, percentile and median for each measure.    What I have created so far s a long table of repeated data.

 

I have tried looking into the Tile tool as an alternative but I got stuck on how to identify the values for the tile splits.

 

Many thanks in advance for any suggestions

 

 

 

 

7 REPLIES 7
ddiesel
13 - Pulsar
13 - Pulsar

Hi @chickenlicken!

 

Is the rank meant to be calculated across the department and year? And the percentile/median across year and measure? If so, I think something like this might work for you.

 

Capture.PNG

 

Capture.PNG

 

Let us know if this gets you closer. If not, please provide a sample of what you want the output to look like.

chickenlicken
8 - Asteroid

HI @ddiesel, thanks for this.  This is the output I am looking for.  The trouble is that for my larger workflow, there are many measures and submeasures (~100)  and I was looking for a method that means I do not have to use the Summarize function and manually reconfigure the workflow each time for each group.

 

Would I be able to use this type of workflow as a macro?

 

Thanks for your help!

chickenlicken
8 - Asteroid

Is it possible to use the multi-row tool to create subtotals and quartiles, rather than the Summarize tool?

ddiesel
13 - Pulsar
13 - Pulsar

Hi @chickenlicken! The workflow is designed to be dynamic across Departments, Years, and Measures. 

 

Example:


Zombie Frogs and Wollypogs in-

Capture.PNG

 

Zombie Frogs and Wollypogs out-

Capture.PNG

 

 

Summarize (7) that summarizes the data by Department and Year might need adjusting for the calculations you need. Percentile_Value and Median_Value are equal. I'm not sure what you need calculated here, and I'm not sure how the multi-row formula would apply.

 

Try this out. If it's still not working for you, please provide a good sample input/output.

ddiesel
13 - Pulsar
13 - Pulsar

@chickenlicken

 

Also please explain the submeasure part. How do you need the measures calculated across which years, departments, and measures?

chickenlicken
8 - Asteroid

Thank you!  I looked more closely at your solution and it works with my larger data set - I had been massively overcomplicating things and using about 10 tools where just one summarize would do.  You are a life saver.

ddiesel
13 - Pulsar
13 - Pulsar

You're welcome! I'm glad it worked out for you.

Labels