Dashboards & Visualizations

## Can somebody help me create a cumulative percentage line for my data?

Motivator

Hello!

I am trying to create a pareto chart that contains a cumulative percentage line. My data consists of time tracking data in which resources charge time to their assigned tasks. I need to sum the total time spent grouped by task and chart it. I've managed to get the result that I want without grouping by the task name:

index="time_tracking" | eventstats sum("Total Hours by Day") as "Total Effort" | sort -"Total Hours by Day" | streamstats sum("Total Hours by Day") as "Cumulative Hours" | eval "Cumulative Percent"=('Cumulative Hours'/'Total Effort')*100 | table "Task Name", "Total Hours by Day", "Cumulative Percent"

It produces output that I can graph (subset below):

Task Name|Total Hours by Day|Cumulative Percent

My problem is that I don't know how to group this data by field "Task Name". Whenever I introduce a new stats command, the calculations break down:

stats sum("Total Hours by Day") as "Total Task Effort" by "Task Name"

I am looking for pointers as to how best to group the data and maintain my calculations. Any input would be greatly appreciated!

Thank you!

Andrew

Tags (1)
1 Solution
Revered Legend

Give this a try

``````index="time_tracking" | eventstats sum("Total Hours by Day") as "Total Effort" by "Task Name" | sort "Task Name",-"Total Hours by Day" | streamstats sum("Total Hours by Day") as "Cumulative Hours" by "Task Name" | eval "Cumulative Percent"=('Cumulative Hours'/'Total Effort')*100 | table "Task Name", "Total Hours by Day", "Cumulative Percent"
``````

If this is not what you want, please share your sample data and expected result based off the same data.

Update
The expected output made it much easier to understand and here is the query that I think would work for you.

``````index="time_tracking"
| stats sum("Total Hours by Day") as "Total Hours by Day" by "Task Name"
| eventstats sum("Total Hours by Day") as "Total Effort" | sort -"Total Hours by Day" | streamstats sum("Total Hours by Day") as "Cumulative Hours"  | eval "Cumulative Percent"=('Cumulative Hours'/'Total Effort')*100 | table "Task Name", "Total Hours by Day", "Cumulative Percent"
``````
Revered Legend

Give this a try

``````index="time_tracking" | eventstats sum("Total Hours by Day") as "Total Effort" by "Task Name" | sort "Task Name",-"Total Hours by Day" | streamstats sum("Total Hours by Day") as "Cumulative Hours" by "Task Name" | eval "Cumulative Percent"=('Cumulative Hours'/'Total Effort')*100 | table "Task Name", "Total Hours by Day", "Cumulative Percent"
``````

If this is not what you want, please share your sample data and expected result based off the same data.

Update
The expected output made it much easier to understand and here is the query that I think would work for you.

``````index="time_tracking"
| stats sum("Total Hours by Day") as "Total Hours by Day" by "Task Name"
| eventstats sum("Total Hours by Day") as "Total Effort" | sort -"Total Hours by Day" | streamstats sum("Total Hours by Day") as "Cumulative Hours"  | eval "Cumulative Percent"=('Cumulative Hours'/'Total Effort')*100 | table "Task Name", "Total Hours by Day", "Cumulative Percent"
``````
Motivator

Thank you for taking the time to respond! This is a step closer I think, but not exactly what I'm looking for. I've got some new ideas to work with. As requested, here is some sample data and my expected result:

Sample Data

``````Task Name|Total Hours by Day
``````

Expected Output

``````Task Name|Total Hours by Day|Cumulative Percent
``````

Basically it sums the "Total Hours by Day", grouped by "Task Name", and calculates the "Cumulative Percent" based on the sum of "Total Hours by Day" and "Total Hours by Day" for the given "Task Name".

Get Updates on the Splunk Community!

#### Detecting Remote Code Executions With the Splunk Threat Research Team

WATCH NOWRemote code execution (RCE) vulnerabilities pose a significant risk to organizations. If exploited, ...

#### Enter the Splunk Community Dashboard Challenge for Your Chance to Win!

The Splunk Community Dashboard Challenge is underway! This is your chance to showcase your skills in creating ...

#### .conf24 | Session Scheduler is Live!!

.conf24 is happening June 11 - 14 in Las Vegas, and we are thrilled to announce that the conference catalog ...