To Calculate Percentile in Beast Mode

user01683

Hi Team,

 

I couldn't able to see percentile function in beast mode. Is it possible to add the function?. I can see in other visualaization like graphs. I need to do calculate in Tabular format.  Is there anyway to do that calculation in tabular format.

 

Thanks in Advance.

jhl

Hi,

 

afaik the latest stable version of MySQL (5.7) that Beast Mode uses (I think that's it, right, moderators?) doesn't actually have that function (fingers crossed for version 8.0, which also has window functions, which will be absolutely mindblowing. 

 

What you can do however is add a rank into your data (for each column individually that you want to have a percentile for) using a MySQL dataflow (dont forget to ORDER BY `desired column` [ASC or DESC]) or with ETL. 

This would, for example, give you a rank, of, say, 1 for the largest datapoint (odering descending). You nee the rank, since you want to know what percentage of your data is above or below that. If your data has 1000 rows, that would mean that this value is in the 99th percentile - all you need to do is to add a beast mode (you could also do this in MySQL if you want) and you're golden.

 

I am not entirely sure what you mean by "need a tabular format" but I assume as soon as you have your percentile value, you can just put it into a table graph next to, say, name of what you are ranking and its values. If you are aggregating values here, you will have to perform your rank on the aggregation, though.

 

NOTE: I haven't put an example calculation in here because "percentile" can mean a bunch of things - there are slight differences to take into account, but you will need a rank for your values no matter what.

saipawar

Hi @jhl, 

Following up with your example of 1000 rows in the dataset, how do you know that the largest value is in the 99th percentile with only the order function? Is there a way to convert those 1000 values into the percentile? Can you share a sample beast mode please? 

Thanks,

Sailee