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
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
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
Ah , that was it, I needed "chart", not "stats"
Thank you!
Chris
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
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
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
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.
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