Multiple Stats Amounts using a Lookup Table

[chrisboy68]

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

[acharlieh · Accepted solution]

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

[chrisboy68]

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

Thank you!

Chris

[hogan24]

If I'm understanding your question correctly, I believe something like this should work:
| eval GrandTotal = SubTotal1+SubTotal2+SubTotal3
| table Place SubTotal1 SubTotal2 SubTotal3 GrandTotal

[chrisboy68]

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

[hogan24]

Here's another way that I think may be more what you're looking for:
| 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

[hogan24]

Ahh, I think I see what you're saying. You're getting multiple rows and you want to have 1 row with multiple columns, is that correct? If so, try piping the results to 'transpose' (| tranpose) and see if that puts you in the right direction.

[chrisboy68]

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