topic Re: Is there a faster way to calculate percentages faster? in Reporting

Is there a faster way to calculate percentages faster?

cindygibbs_08 — Fri, 02 Jul 2021 18:28:51 GMT

Well Hello Gorgeous people!

I have a fields that can take anywhere from 3 to 5 diferente values which are cities... this field is called "CITY" if I want to get the percentage of each city over the total count I always Have to do something like this:

| stats count(eval(CITY="A")) as CITY_A, count(eval(CITY="B")) as CITY_B, count(eval(CITY="C")) as CITY_C, count(CITY) as TOTAL | eval %P_CITY_A=CITY_A/TOTAL (repeat for each city)

but often times I find myself wanting to calculate percetages of the values of fields that can have up to 15 differente values.. and I just wonder is there is a faster more effcient way of doing this.... thank you so much people

love,

cindy

Re: Is there a faster way to calculate percentages faster?

richgalloway — Fri, 02 Jul 2021 20:22:11 GMT

At first I thought about using FOREACH, but I'm sure the real city names don't all start with "CITY_" so that won't work.

This does work, however, but it's a little ugly.

| makeresults | eval CITY="Boston;Sydney;London;Boston;Paris;Cairo;Paris;London" | eval CITY=split(CITY,";") | mvexpand CITY ```Everything above sets up test data. Don't use in the real query``` ```Get counts for each city``` | stats count by CITY ```Get the total count and assign it to bogus city "A" (don't change that)``` ```Get the total count and add it as a field to each row``` | eventstats sum(count) as TOTAL ```Compute the percentage``` | eval PCT=count*100/TOTAL ```Remove the TOTAL field (optional)``` | fields - TOTAL

Re: Is there a faster way to calculate percentages faster?

bowesmana — Fri, 02 Jul 2021 23:49:56 GMT

The top command does just that

As for whether this is faster than doing eventstats the calculating percentages, I am not sure, but it's certainly less code