I have a table of data with values like this:
String Numeric
Client 1 99.9
Client 2 99.2
Client 3 93.8
Client 4 89.0
Client 5 72.9
and so on for miles....
I would like to add a column which outputs Decile rank of the clients based on their numeric value. Can you point me in the right direction please?
Thank you,
-MD
Does this example work for you?
Before eventstats is just setting up example data
| makeresults count=143
| streamstats c
| eval Client="Client ". c
| fields - _time c
| eval Numeric=random() % 1000 / 10
``` Logic from here to calculate deciles ```
| eventstats perc10(Numeric) as p10 perc20(Numeric) as p20 perc30(Numeric) as p30 perc40(Numeric) as p40 perc50(Numeric) as p50 perc60(Numeric) as p60 perc70(Numeric) as p70 perc80(Numeric) as p80 perc90(Numeric) as p90
``` Now position the client according to rank ```
| foreach 1 2 3 4 5 6 7 8 9 [ eval Rank=if(isnull(Rank) AND Numeric < 'p<<FIELD>>0', <<FIELD>>, Rank) ]
| fillnull Rank value=10
| fields - p*
Does this example work for you?
Before eventstats is just setting up example data
| makeresults count=143
| streamstats c
| eval Client="Client ". c
| fields - _time c
| eval Numeric=random() % 1000 / 10
``` Logic from here to calculate deciles ```
| eventstats perc10(Numeric) as p10 perc20(Numeric) as p20 perc30(Numeric) as p30 perc40(Numeric) as p40 perc50(Numeric) as p50 perc60(Numeric) as p60 perc70(Numeric) as p70 perc80(Numeric) as p80 perc90(Numeric) as p90
``` Now position the client according to rank ```
| foreach 1 2 3 4 5 6 7 8 9 [ eval Rank=if(isnull(Rank) AND Numeric < 'p<<FIELD>>0', <<FIELD>>, Rank) ]
| fillnull Rank value=10
| fields - p*
Perfectly...Thank you @bowesmana !