Finding quartiles and outputting the data within each quartile

artywah
artywah Member
in Magic ETL

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

  • Darius
    Darius Contributor
    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"

Answers

  • Darius
    Darius Contributor
    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"

Categories