Modifying the total value of revenue within a piechart

[ablumenthal_spl]

I am trying to graph a pie chart that reflects the percentage that TopTenRevenue is on TotalRevenue. The search string I've written out causes it to add the two values (TopTenRevenue and TotalRevenue) together before dividing TopTenRevenue into TotalRevenue giving me an answer around 32%. The answer should be approximately 47%. I've attached my Statistics table below as well as my search string. Any help would be appreciated.

Search String :

sourcetype=csv "Fiscal Period"="*-2014" Stage="Closed Won (100%)" "Account Name"="*" | stats sum(Amount) by "Account Name" | stats sum | rename sum(sum(Amount)) as TotalRevenue | table TotalRevenue | appendcols [search sourcetype=csv "Fiscal Period"="*-2014" Stage="Closed Won (100%)" "Account Name"="*" | stats sum(Amount) by "Account Name" | sort sum(Amount) by "Account Name" | tail 10 | stats sum(sum(Amount)) ] | rename sum(sum(Amount)) as TopTenRevenue | table TopTenRevenue TotalRevenue | eval finalValue=TopTenRevenue/TotalRevenue * 100 | rename finalValue as Percentage | transpose | rename column as Period | rename "row 1" as Amount 

[woodcock]

OK, I went ahead and cleaned up some things and restated them as follows:

1) The first thing I'm trying to do is calculate the sum of my Total Revenue for all of my data from 2014.

sourcetype=csv "Fiscal Period"="-2014" Stage="Closed Won (100%)" "Account Name"="" | stats sum(Amount) by "Account Name" AS TotalRevenueByAccount | stats sum(TotalRevenueByAccount) AS TotalRevenue

2) Next I run the same search, but I'm looking to find the top ten values per account. I use the appendcols function to do this. For ease of understanding, I've removed it so it can be run as a separate search

sourcetype=csv "Fiscal Period"="-2014" Stage="Closed Won (100%)" "Account Name"="" | stats sum(Amount) by "Account Name" AS TotalRevenueByAccount | top limit=10 TotalRevenueByAccount | stats sum(TotalRevenueByAccount) AS TopTenRevenue

3) Combined, this makes the search this:

sourcetype=csv "Fiscal Period"="-2014" Stage="Closed Won (100%)" "Account Name"="" | stats sum(Amount) by "Account Name" AS TotalRevenueByAccount | stats sum(TotalRevenueByAccount) AS TotalRevenue | appendcols [search sourcetype=csv "Fiscal Period"="-2014" Stage="Closed Won (100%)" "Account Name"="" | stats sum(Amount) by "Account Name" AS TotalRevenueByAccount | top limit=10 TotalRevenueByAccount | stats sum(TotalRevenueByAccount) AS TopTenRevenue]

4) Given the two results that I have now, I'm looking to create a pie chart that reflects the TopTenRevenue divided by TotalRevenue. So this should work:

sourcetype=csv "Fiscal Period"="-2014" Stage="Closed Won (100%)" "Account Name"="" | stats sum(Amount) by "Account Name" AS TotalRevenueByAccount | stats sum(TotalRevenueByAccount) AS TotalRevenue | appendcols [search sourcetype=csv "Fiscal Period"="-2014" Stage="Closed Won (100%)" "Account Name"="" | stats sum(Amount) by "Account Name" AS TotalRevenueByAccount | top limit=10 TotalRevenueByAccount | stats sum(TotalRevenueByAccount) AS TopTenRevenue] | eval OtherRevenue = TotalRevenue - TopTenRevenue | fields TotalRevenue OtherRevenue | transpose

[woodcock]

I dislike the looks of your existing search (doesn't make sense to me). Will you back up and show some sample events and explain what you are trying to do with them. Back up a few steps and start your explanation over.

[ablumenthal_spl]

1) The first thing I'm trying to do is calculate the sum of my Total Revenue for all of my data from 2014.

sourcetype=csv "Fiscal Period"="-2014" Stage="Closed Won (100%)" "Account Name"="" | stats sum(Amount) by "Account Name" | stats sum | rename sum(sum(Amount)) as TotalRevenue | table TotalRevenue

**The value that Splunk returns is TotalRevenue: 4831805.82 **

2) Next I run the same search, but I'm looking to find the top ten values per account. I use the appendcols function to do this. For ease of understanding, I've removed it so it can be run as a separate search-

sourcetype=csv "Fiscal Period"="-2014" Stage="Closed Won (100%)" "Account Name"="" | stats sum(Amount) by "Account Name" | sort sum(Amount) by "Account Name" | tail 10 | stats sum(sum(Amount)) | rename sum(sum(Amount)) as TopTenRevenue

*** Splunk returns the result TopTenRevenue:2281605.54 ***

3) Given the two results that I have now, I'm looking to create a pie chart that reflects the TopTenRevenue divided by TotalRevenue.

I think this is where my issue is created.

**I've eliminated the eval function in the previous string as I don't think it's necessary **

After I close my bracket and pipe the previous step, I table TopTenRevenue and TotalRevenue.

Because the results present themselves in two columns, I transpose them to put them in rows so that they can go into a pie graph.

| table TopTenRevenue TotalRevenue | transpose | rename column as Period | rename "row 1" as Amount

4) When I go to graph this, Splunk adds the values for TotalRevenue and TopTenRevenue. The graph reflects that TopTenRevenue(2281605.54)/(7113411.36 or the sum of TopTen and Total)

I need the graph to reflect (2281605.54 or TopTenRevenue)/(4831805.82 orTotalRevenue)

It looks like I'm probably over complicating the search string and could do without one of the steps. Does this make more sense to you? what would the best approach be to achieve my desired result? Thanks for your help.