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
Solved! Go to Solution.
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.
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!
Is it possible to use the multi-row tool to create subtotals and quartiles, rather than the Summarize tool?
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.
Also please explain the submeasure part. How do you need the measures calculated across which years, departments, and measures?
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.
You're welcome! I'm glad it worked out for you.