Batch macro. and multiple filters to create ranks and percentiles

[chickenlicken]

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

[ddiesel]

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.

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

[chickenlicken]

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]

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

[ddiesel · Accepted solution]

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

Example:

Zombie Frogs and Wollypogs in-

Zombie Frogs and Wollypogs out-

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]

@chickenlicken

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

[chickenlicken]

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]

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