Splunk Search

Pivot 201: Sum of amount for each department using Pivot Tables

Splunk2016
Path Finder

I have gone over Splunk's tutorial to create Pivot tables. Now that I know the process,
I would appreciate some direction on how to effectively summarize totals by department ID.
Here is a simple water down sample of my input data:
ID Amount
g0001 20000
g0002 10000
g0001 20000
g0003 20000
g0001 10000
g0004 20000
....

The pivot should provide the following (ID will be on x axis and Total Amount on the y axis for a bar chart):
ID Total Amount
g0001 50000
g0002 10000
g0003 20000
g0004 20000

Splunk requires:
1. tutorialdata.zip to create the pivot data model

2. Prices.csv.zip to create the pivot lookup data

How does Splunk data files translates to my input data?
Is the tutorialdata.zip equivalent to my input data shown above?
Does Splunk require to create from my input data shown above something equivalent to Prices.csv.zip for the Lookup data?
When creating a pivot table, I select "ID" under the split Rows and Count under column values which displays the following result:
ID Count
g0001 3
g0002 1
g0003 1
g0004 1

When creating a pivot table, I select "ID" under the split Rows and Sum for Amount under column values which displays the following result (the sum for Amount shows as blank):
ID Sum
g0001

g0002

g0003

g0004

I would appreciate any comments. Thanks!

Tags (3)
0 Karma
1 Solution

Splunk2016
Path Finder

I ran multiple test using Sample data from Buttercup Games under Excel and was able to compare it to Splunk and see what it was doing. I also found that the Amount I was using included $, so I changed the input data and now it works!

View solution in original post

Splunk2016
Path Finder

I ran multiple test using Sample data from Buttercup Games under Excel and was able to compare it to Splunk and see what it was doing. I also found that the Amount I was using included $, so I changed the input data and now it works!

Splunk2016
Path Finder

I found that the Amount was including $, so I changed the format in the Lookup input and recreated the Lookup table.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...