Finding quartiles and outputting the data within each quartile
Hi
I'm trying to break a dataset down into quartiles for Recency, Frequency, and Monetary (each data item will have a value for each).
I then need to output the items that are within a given quartile in order to do further data manipulation.
I know that box plot graphs can show quartile values, but they don't actually mark up or export the data in a way it can be further used.
I've spent a number of hours looking through SQL forums as I couldn't find any answers here, but my SQL isn't great. The best of found is finding the median of a value https://knowledge.domo.com/Prepare/DataFlow_Tips_and_Tricks/Finding_the_Median_of_a_Value but then how do I go from there to find the quartiles?
Best Answer

artywah,
This sounds like something that you could do in mySQL or Magic ETL. I can think of a couple of ways you could do this:
 Median: You could find your median and then split the data so that the values below the median go into one transform and the ones above your median go into another (you would have to determine which half gets the actual median record). From each transform that has half of the data, you could repeat the same process, splitting the transform into two parts. This would result in 4 parts that represent your quartiles for the metric of interest (recency, frequency, or monetary).
 Ranking: You could rank your data by one of the metrics you referred to and then divide the data based on your total record count (the total would need to be added as a constant in its own column). Splitting up the data could be done with a case statement in SQL or the filter rows tile in Magic ETL. Here is an example of a case statement that could help get you to your end goal:
CASE
WHEN `rank_column_name` < (.25 * `total_row_count_column`) THEN 'Quartile 1'
WHEN `rank_column_name` >= (.25 * `total_row_count_column`) AND`rank_column_name` < (.25 * `total_row_count_column`) THEN 'Quartile 2'
WHEN`rank_column_name` >= (.5 * `total_row_count_column`) AND `rank_column_name` < (.75 * `total_row_count_column`) THEN 'Quartile 3'
WHEN`rank_column_name` >= (.75 * `total_row_count_column`) THEN 'Quartile 4'
END
Good luck as you work on your use case!
Regards,
Darius Rose
**Say “Thanks” by clicking the “heart” in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"2
Answers

artywah,
This sounds like something that you could do in mySQL or Magic ETL. I can think of a couple of ways you could do this:
 Median: You could find your median and then split the data so that the values below the median go into one transform and the ones above your median go into another (you would have to determine which half gets the actual median record). From each transform that has half of the data, you could repeat the same process, splitting the transform into two parts. This would result in 4 parts that represent your quartiles for the metric of interest (recency, frequency, or monetary).
 Ranking: You could rank your data by one of the metrics you referred to and then divide the data based on your total record count (the total would need to be added as a constant in its own column). Splitting up the data could be done with a case statement in SQL or the filter rows tile in Magic ETL. Here is an example of a case statement that could help get you to your end goal:
CASE
WHEN `rank_column_name` < (.25 * `total_row_count_column`) THEN 'Quartile 1'
WHEN `rank_column_name` >= (.25 * `total_row_count_column`) AND`rank_column_name` < (.25 * `total_row_count_column`) THEN 'Quartile 2'
WHEN`rank_column_name` >= (.5 * `total_row_count_column`) AND `rank_column_name` < (.75 * `total_row_count_column`) THEN 'Quartile 3'
WHEN`rank_column_name` >= (.75 * `total_row_count_column`) THEN 'Quartile 4'
END
Good luck as you work on your use case!
Regards,
Darius Rose
**Say “Thanks” by clicking the “heart” in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"2
Categories
 All Categories
 1.1K Product Ideas
 1.1K Ideas Exchange
 1.2K Connect
 966 Connectors
 254 Workbench
 Cloud Amplifier
 1 Federated
 2.4K Transform
 75 SQL DataFlows
 499 Datasets
 1.8K Magic ETL
 2.7K Visualize
 2.2K Charting
 360 Beast Mode
 19 Variables
 483 Automate
 101 Apps
 378 APIs & Domo Developer
 6 Workflows
 22 Predict
 6 Jupyter Workspaces
 16 R & Python Tiles
 316 Distribute
 64 Domo Everywhere
 252 Scheduled Reports
 59 Manage
 59 Governance & Security
 1 Product Release Questions
 5K Community Forums
 37 Getting Started
 23 Community Member Introductions
 63 Community Announcements
 4.8K Archive