Hello, I'm attempting to display three calculated fields (totalmeetinghours, totalusenomeetinghours, and hoursnotin_use) as a part of a pie chart. Each of these fields should represent a calculated portion of the total pie chart, with their values given on mouse over and their field names showing as their labels for their respective portions on the graph.
I'm currently attempting to achieve this (seen below) by using mvappend to create a multi-value field (displayval) and then split on that field in the chart function. However when splitting by this field it displays the numeric time values for each of the fields instead of the field names (i.e "11.023" hour time instead of "totalmeeting_hours"). How would I go about accomplishing this? I attempted to create a separate multi-value field and combine it with the other for labeling, but have so far been unable to do so. I'd greatly appreciate help!
| convert num(SM_C.value.data.elapsedTime) as use_duration num(SM_C.value.data.MeetingElapsedTime) as meeting_duration | where (use_duration < 50000 and use_duration > 0) | eval meeting_duration = if(meeting_duration < use_duration, meeting_duration, meeting_duration = 0) | stats sum(use_duration) as total_duration sum(meeting_duration) as total_meeting_duration | eval total_meeting_hours = (total_meeting_duration / 60 / 60) | eval total_use_no_meeting_hours = (total_duration / 60 / 60) - total_meeting_hours | eval hours_not_in_use = (168) - (total_duration / 60 / 60) | eval display_val=(mvappend(hours_not_in_use, total_use_no_meeting_hours, total_meeting_hours)) | eval display_label=(mvappend("hours_not_in_use", "total_use_no_meeting_hours", "total_meeting_hours")) | mvexpand display_val | mvexpand display_label | chart eval(sum(display_val) / 168) as Percentage by display_val
How about something like this ...
| convert num(SM_C.value.data.elapsedTime) as use_duration num(SM_C.value.data.MeetingElapsedTime) as meeting_duration | where (use_duration < 50000 and use_duration > 0) | eval meeting_duration = if(meeting_duration < use_duration, meeting_duration, meeting_duration = 0) | stats sum(use_duration) as total_duration sum(meeting_duration) as total_meeting_duration | eval used_hours = round((total_meeting_duration / 3600),2) | eval reserved_hours = round((total_duration / 3600),2) | eval unused_hours = 168-reserved_hours | eval reserved_unused = reserved_hours - used_hours | eval fan=mvrange(1,3.0001) | mvexpand fan | eval Usage=case(fan=1,"Used", fan="2","Reserved Not Used", fan=3,"Unused") | eval Value=case(fan=1,used_hours, fan="2",reserved_unused, fan=3,unused_hours) | chart sum(Value) by Usage
Thank you very much! That answer is what I needed to get it. I made a few changes to it to have it work with my data, as I'm separating room use into both "occupied in call / meeting" vs "occupied only", apart from the room being out of use. But eval's case() function was exactly the solution. Thanks! This was my final solution:
| convert num(SMC.value.data.elapsedTime) as useduration num(SMC.value.data.MeetingElapsedTime) as meetingduration
| where (useduration < 50000 and useduration > 0)
| eval meetingduration = if(meetingduration < useduration, meetingduration, meetingduration = 0)
| stats sum(useduration) as totalduration sum(meetingduration) as totalmeetingduration
| eval meetinghours = round((totalmeetingduration / 3600), 2)
| eval usednomeetinghours = round(((totalduration / 3600) - meetinghours), 2)
| eval hoursnotinuse = round((168) - (totalduration / 3600), 2)
| eval fan=mvrange(1, 3.0001)
| mvexpand fan
| eval Usage=case(fan=1,"Occupied", fan=2,"Meeting/Call", fan=3,"Unoccupied")
| eval Value=case(fan=1,usednomeetinghours, fan=2,meetinghours, fan=3,hoursnotin_use)
| chart sum(Value) by Usage
@pjbuchan596 - You're welcome. Thanks for posting the final solution. It helps folks to make their own connections.
I had understood "used-no-meeting" incorrectly, I had assumed that was when someone had reserved the room but not actually occupied it.
Hey @pjbuchan596, if DalJeanis solved your problem, remember to "√Accept" an answer to award karma points 🙂