Create a pivot table
In the previous topic you used pivot to find the total number of purchase requests and saved the single value display as a report. In this topic, you will use the pivot visualization editor to create a pivot table of the Buttercup Games Successful Purchases object.
The Successful Purchases object has attributes for the products purchased from the Buttercup Games website. This includes the automatically extracted attributes (categoryId and productId) as well as the lookup attributes (price and product_name).
The Buttercup Games online store offers hundreds of products, of a variety of categories, and you want to know more about the items that were purchased over the past week. You can create a pivot report that breaks down the total number of purchase events by product name, and through that quickly see which of your products were the top sellers for that period.
Define a new Pivot
1. From the app navigation bar, select Pivot to enter the "Select a Data Model" page.
2. Choose the Buttercup Games data model and select the Successful Purchases child object.
The New Pivot editor for Successful Purchases opens.
Add pivot elements
You can add multiple elements from each pivot element category to define your pivot table. It's easy to add, define, and remove pivot elements in the process of determining what information your table should provide.
- To add a pivot element: Click the + icon. This opens up the element dialog, where you choose an attribute and then define how the element uses that attribute.
- To inspect or edit an element: Click the "pencil" icon on the element. This opens the element dialog.
- To reorder and transfer pivot elements: Drag and drop an element within its pivot element category to reorder it. Drag and drop elements between element categories to transfer them.
- To remove pivot elements from the Pivot Editor: Open its element dialog and click the Remove button, or drag the element up or down until it turns red and drop it.
Under Filters, the time filter is always present when you build a pivot; you cannot remove it. It defines the time range for which the pivot returns results. It operates exactly like the time range menu that is in use throughout Splunk Web. For more information, see "Select time ranges to apply to your search" in the Search Manual.
Change the time range filter
Currently your Pivot table shows a single value, the total count of Successful Purchases over All time.
Change the time filter to view the Successful Purchases over a different time range:
1. Under Filter, click the pencil next to All time to open the time range picker.
2. Under Presets and Relative, click "Last 7 days".
(If this shows no events, you can select "All time" and continue.)
Add a Split Row element
Add Pivot elements to see the Count of Successful Purchases for each product by name:
1. Under Split Rows, click + and select
productName, the lookup field that contains the name of each product, based on the
This opens a dialog box that lets you format the field.
2. Rename the field, Product Name and Click Add To Table.
Add a Column Value element
Add a Column Value to see total earned for each product that was successfully purchased:
1. Under Column Values, click + and select
2. In the dialog box, format the field:
2.a Enter the label Total Revenue.
2.b Select the Value Sum.
This creates a field called Total Revenue, which is the summation of the price for each successful purchase of the product. (You can add the
price values as another Split Row, if you want to see the cost of each individual product in this table.)
3. Click Add To Table.
Save the Pivot table
Save the Pivot table as a report named Purchases by Product.
1. Click Save as and select Report.
2. In the Save as Report dialog box:
2.a Enter the Title "Purchases by Product".
2.b (Optional) Add the Description "Table of Product Purchases".
2.c Include a Time Range Picker.
3. Click Save.
4. In the Your Report Has Been Created dialog box, click View.
Continue to the next topic to create some simple pivot visualizations.
Create and save a Pivot
Create a pivot chart
This documentation applies to the following versions of Splunk® Enterprise: 6.2.0, 6.2.1, 6.2.2, 6.2.3, 6.2.4, 6.2.5, 6.2.6, 6.2.7, 6.2.8, 6.2.9, 6.2.10, 6.2.11, 6.2.12, 6.2.13, 6.2.14, 6.2.15, 6.3.0, 6.3.1, 6.3.2, 6.3.3, 6.3.4, 6.3.5, 6.3.6, 6.3.7, 6.3.8, 6.3.9, 6.3.10, 6.3.11, 6.3.12, 6.3.13, 6.3.14, 6.4.0, 6.4.1, 6.4.2, 6.4.3, 6.4.4, 6.4.5, 6.4.6, 6.4.7, 6.4.8, 6.4.9, 6.4.10, 6.4.11