I have a search result that is tabled, and now, I am trying to find a result by percentage of the location they visit most, A B or C per person.
Noting that each location A, B or C ,have sub locations designated by numbers, e.g. A1234, A2345, A4567 etc.
I'm trying to find the total % based on primary designation e.g. A, B or C*.
I can stats sum the events by sub location, but I can't seem to work out how to group the primary designations as a %.
ID Patron Location 123 Bob A1234 234 Shirley A1234 123 Bob B4264 123 Bob C7894 234 Shirley B4356 123 Bob A4561
Location ID Patron A B C 123 Bob X% X% X% 234 Shirley Y% Y% Y%
Thank you all in advance for your help.
Can you try this and see if it matches your requirements?
| makeresults | eval ID="123,234,123,123,234,123"| makemv ID delim="," | mvexpand ID | appendcols [| makeresults | eval Patron="Bob,Shirley,Bob,Bob,Shirley,Bob"| makemv Patron delim=","| mvexpand Patron] | appendcols [| makeresults| eval Location="A1234,A1234,B4264,C7894,B4356,A4561"| makemv Location delim=","| mvexpand Location] | rename COMMENT as "---All above is for dummy data generation---" | eval PrimeLocation=substr(Location,0,1)| eventstats count by Patron,PrimeLocation | eventstats sum(count) as total| eval perc=round((count/total)*100,2) | stats values(eval(if(PrimeLocation=="A",perc,null()))) as A ,values(eval(if(PrimeLocation=="B",perc,null()))) as B,values(eval(if(PrimeLocation=="C",perc,null()))) as C by ID,Patron
Thank you for that. looks great unfortunately it doesn't seem work with the rest of my search. I'll have a look and see if i can modify it a bit. I'm getting a search conflict error.