calculate percentage from the results of two reports

[jdlocklin526]

I'm having a difficult time calculating a percentage based on two reports (searches).

Search 1
| inputlookup mydata.csv
| where category= "category" AND assignment_group="myteam" AND yyyy_mm="$Datedropdown$"
| stats count as myteamstotal

Search 2
| inputlookup mydata.csv
| where category= "category" AND yyyy_mm="$Datedropdown$"
| stats count as total

I'd like to calculate a percentage
| eval percent=(myteamstotal/total)*100

I tried this and got 0 as a result:

| inputlookup GCC-SNOW_01012018_12312018.csv
| where category= "category" AND assignment_group="myteam" AND yyyy_mm="$Datedropdown$"
| stats count as myteamstotal
| where category= "category" AND yyyy_mm="$Datedropdown$"
| stats count as total
| eval percent=(myteamstotal/total)*100

How can I calculate the percentage?

[adonio · Accepted solution]

maybe try | appendcols
something like this:

| inputlookup mydata.csv 
| where category= "category" AND assignment_group="myteam" AND yyyy_mm="$Datedropdown$"
| stats count as myteamstotal
| appendcols [ | inputlookup mydata.csv 
| where category= "category" AND yyyy_mm="$Datedropdown$"
| stats count as total
| eval percent=(myteamstotal/total)*100

hope it helps

[jdlocklin526]

Thanks Adonio!

I just needed to close the brackets in the appendcols. Final query was:

| inputlookup mydata.csv
| where category= "category" AND assignment_group="myteam" AND yyyy_mm="$Datedropdown$"
| stats count as myteamstotal
| appendcols [ inputlookup mydata.csv
| where category= "category" AND yyyy_mm="$Datedropdown$"
| stats count as total]
| eval percent=(myteamstotal/total)*100
| fields percent

As an intermediate step to check any issues, you can view fields percent, myteamstotal, total as a table.

[HiroshiSatoh]

Try this!

| inputlookup mydata.csv
| where category= "category" AND yyyy_mm="$Datedropdown$"
| stats count(eval(assignment_group="myteam")) as myteamstotal,count as total
| eval percent=(myteamstotal/total)*100