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
Categories
 7.7K All Categories
 3 Connect
 919 Connectors
 244 Workbench
 477 Transform
 1.8K Magic ETL
 60 SQL DataFlows
 446 Datasets
 37 Visualize
 198 Beast Mode
 2K Charting
 8 Variables
 1 Automate
 348 APIs & Domo Developer
 82 Apps
 Workflows
 14 Predict
 3 Jupyter Workspaces
 11 R & Python Tiles
 241 Distribute
 59 Domo Everywhere
 241 Scheduled Reports
 15 Manage
 36 Governance & Security
 27 Product Ideas
 1.1K Ideas Exchange
 Community Forums
 14 Getting Started
 1 Community Member Introductions
 49 Community News
 18 Event Recordings
 579 日本支部