Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Splunk Search

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Community
- :
- Splunk Answers
- :
- Using Splunk
- :
- Splunk Search
- :
- Re: Multiple Stats Amounts using a Lookup Table

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark Topic
- Subscribe to Topic
- Mute Topic
- Printer Friendly Page

- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

chrisboy68

Contributor

06-04-2015
12:03 PM

HI,

Can't seem to get this working. This is what I want, so I can do a multi stacked bar chart.

Columns:

Place, SubTotal 1, SubTotal 2, SubTotal 3, Grand Total.

My lookup table will have 3 rows for each place.

Place, SubPlace 1

Place, SubPlace 2

Place SubPlace 3

I have a search where I find sales amount by each SubPlace:

```
mysearch| lookup sales_lookup SalesID as SalesID OUTPUT Place, SalesType | stats sum(SalesRevenue) as SalesTypeTotal by SalesType
```

I can't figure out how to have it all on one row so I have : Place, SubTotal 1, SubTotal 2, SubTotal 3, Grand Total.

Any ideas? This should be easy ...

Chris

1 Solution

- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

acharlieh

Influencer

06-04-2015
01:16 PM

Building off of your comment to @hogan24 above, you currently have:

```
mysearch | lookup salesID_lookup SalesID as SalesID OUTPUT Place, SalesType | stats sum(SalesRevenue) as SalesTypeTotal by SalesType
```

But you want the sum of each sales type over place grouped by SalesType, so instead of stats, lets start with trying chart which can do exactly that. (aside: As needs change there are more esoteric ways of building such a table here such using stats followed by xyseries which lets you do some interesting manipulations in between the two commands but that will be down the line when the basic chart command stops fitting your use case)

```
mysearch | lookup salesID_lookup SalesID as SalesID OUTPUT Place, SalesType | chart sum(SalesRevenue) over Place by SalesType
```

This should give you a result table like this:

```
Place Type1 Type2 Type3
Somewhere 123 456 789
SomewhereElse 111 222 333
```

Now if you're wanting totals for each place, assuming the places are non-numeric that's as easy as then piping to addtotals and playing with the options you want for example:

```
mysearch | lookup salesID_lookup SalesID as SalesID OUTPUT Place, SalesType | chart sum(SalesRevenue) over Place by SalesType | addtotals col=true labelfield=Place
```

Assuming we're building on the data from the last step this should now look like:

```
Place Type1 Type2 Type3 Total
Somewhere 123 456 789 1368
SomewhereElse 111 222 333 666
Total 234 678 1122 2034
```

- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

acharlieh

Influencer

06-04-2015
01:16 PM

Building off of your comment to @hogan24 above, you currently have:

```
mysearch | lookup salesID_lookup SalesID as SalesID OUTPUT Place, SalesType | stats sum(SalesRevenue) as SalesTypeTotal by SalesType
```

But you want the sum of each sales type over place grouped by SalesType, so instead of stats, lets start with trying chart which can do exactly that. (aside: As needs change there are more esoteric ways of building such a table here such using stats followed by xyseries which lets you do some interesting manipulations in between the two commands but that will be down the line when the basic chart command stops fitting your use case)

```
mysearch | lookup salesID_lookup SalesID as SalesID OUTPUT Place, SalesType | chart sum(SalesRevenue) over Place by SalesType
```

This should give you a result table like this:

```
Place Type1 Type2 Type3
Somewhere 123 456 789
SomewhereElse 111 222 333
```

Now if you're wanting totals for each place, assuming the places are non-numeric that's as easy as then piping to addtotals and playing with the options you want for example:

```
mysearch | lookup salesID_lookup SalesID as SalesID OUTPUT Place, SalesType | chart sum(SalesRevenue) over Place by SalesType | addtotals col=true labelfield=Place
```

Assuming we're building on the data from the last step this should now look like:

```
Place Type1 Type2 Type3 Total
Somewhere 123 456 789 1368
SomewhereElse 111 222 333 666
Total 234 678 1122 2034
```

- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

chrisboy68

Contributor

06-04-2015
01:29 PM

Ah , that was it, I needed "chart", not "stats"

Thank you!

Chris

- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

hogan24

Path Finder

06-04-2015
12:19 PM

| eval GrandTotal = SubTotal1+SubTotal2+SubTotal3

| table Place SubTotal1 SubTotal2 SubTotal3 GrandTotal

- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

chrisboy68

Contributor

06-04-2015
12:45 PM

Hmm, but I can't figure out how to do that in a search query.

For example, considering I have 3 sales types for each Place named, subTotal1, SubTotal2 and SubTotal3, executing this query will return

mysearch | lookup salesID_lookup SalesID as SalesID OUTPUT Place, SalesType | stats sum(SalesRevenue) as SalesTypeTotal by SalesType

SubTotal1 1000

SubTotal2 1200

SubTotal2 1100

What I want is:

Place, SubTotal1, SubTotal2, SubTotal3, Grand Total.

Where Grand Total is the total of all the SubTotals for each Place. Hope I'm explaining it correctly.

Thank you

Chris

- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

hogan24

Path Finder

06-04-2015
12:57 PM

| eval SubTotal1 = if(columnName=="SubTotal1", countColumnName, null)

| eval SubTotal2 = if(columnName=="SubTotal2", countColumnName, null)

| eval SubTotal3 = if(columnName=="SubTotal3", countColumnName, null)

| table SubTotal1 SubTotal2 SubTotal3

| stats sum(SubTotal1) as SubTotal1 sum(SubTotal2) as SubTotal2 sum(SubTotal3) as SubTotal3

| eval GrandTotal = SubTotal1+SubTotal2+SubTotal3

- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

hogan24

Path Finder

06-04-2015
12:49 PM

- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

chrisboy68

Contributor

06-04-2015
01:18 PM

This didn't work. it made it close, but did not group the Place. For the same place, it duplicated a row for each SubTotal.

This is what I'm looking for on one row:

Place, SubTotal1, SubTotal2, SubTotal3, Grand Total.

Thanks for your help.

Chris

State of Splunk Careers

Find out what your skills are worth!

Read the report >