How to get the sum according to field values?

[auaave]

Hi Guys,

I am counting the number of events from field name "LOCATION".This Field have 4 locations, Location A,B,C and D.
I need to get the count of events from Location A B and C and name it as Position 1 Events then events from Location D as Position 2.
Then after that, I need to get the percentage of events from Position 1 (ABC) and Position 2 (D).

How can I do that?

Thank you!

[micahkemp · Accepted solution]

One option is to use stats with count(eval()) to get counts of each location, then determine the percent at the end (I'm not sure which direction you want the percent, so you may need to flip that portion:

    | makeresults
    | append [| makeresults | eval LOCATION="A"]
    | append [| makeresults | eval LOCATION="B"]
    | append [| makeresults | eval LOCATION="C"]
    | append [| makeresults | eval LOCATION="D"]
    | stats count(eval(LOCATION="A" OR LOCATION="B" OR LOCATION="C")) AS "Position 1 Events", count(eval(LOCATION="D")) AS "Position 2 Events"
    | eval Position1Pct='Position 1 Events'/('Position 1 Events'+'Position 2 Events')*100, Position2Pct='Position 2 Events'/('Position 1 Events'+'Position 2 Events')*100

Another option is to use something closer to a lookup (faked with a case statement here) to translate LOCATION to position, then perform the same count/eval method:

    | makeresults
    | append [| makeresults | eval LOCATION="A"]
    | append [| makeresults | eval LOCATION="B"]
    | append [| makeresults | eval LOCATION="C"]
    | append [| makeresults | eval LOCATION="D"]
    | eval position=case(LOCATION="A" OR LOCATION="B" OR LOCATION="C", 1, LOCATION="D", 2)
    | stats count(eval(position=1)) AS "Position 1 Events", count(eval(position=2)) AS "Position 2 Events"
    | eval Position1Pct='Position 1 Events'/('Position 1 Events'+'Position 2 Events')*100, Position2Pct='Position 2 Events'/('Position 1 Events'+'Position 2 Events')*100

[auaave]

Hi @ micahkemp, thanks for your reply! It worked! I am just having problems with my % calculation.
I think I didn't make it clear enough. Sorry for that.
%A = (Position1/(Position1 + Position2) * 100
%B = (Position2/(Position1 + Position2) * 100
and the number format should be in %
I can only use the division function but I can't combine it with the sum.

Thank you!

[auaave]

@micahkemp, thanks a lot! 🙂

[micahkemp]

Updated answer to reflect the percentage.