Splunk Search

## Where clause with eval and stats

Contributor

Hi All.
I want to calculate percent of Total revenue in Rural and Urban areas.
The columns i have are Total_Revenue and PLACEMENT with values 0 and 1 where 0 represents Rural and 1 represents Urban.

`````` sourcetype="Customer_Churn"
| eventstats sum(Total_Revenue) as fin_rev
| eventstats sum(Total_Revenue) as rural_rev where PLACEMENT=0
| eventstats sum(Total_Revenue) aa urban_rev where PLACEMENT=1
| eval REVENUE=rur_rev*100/urban_rev by PLACEMENT
| replace 0 with Rural in PLACEMENT
| replace 1 with Urban in PLACEMENT
``````

the above query doesnt get executed. please improvise that.

Tags (4)
1 Solution
Motivator

I think you're making this harder than it needs to be. Try this:

`````` sourcetype="Customer_Churn"
| stats sum(Total_Revenue) as rev by PLACEMENT
| eventstats sum(rev) as fin_rev
| eval REVENUE = rev*100/fin_rev
| table PLACEMENT REVENUE
| replace 0 with Rural in PLACEMENT
| replace 1 with Urban in PLACEMENT
``````

`stats` will separate the sums for you; there's no need to compute them separately. Once that's done, you can use `eventstats` to get the overall total, since you're just summing everything.

Esteemed Legend

Try this:

``````sourcetype="Customer_Churn" | stats sum(Total_Revenue) as revenue by PLACEMENT | replace "0" with "rural_rev" in PLACEMENT | replace "1" with "urban_rev" in PLACEMENT | untable PLACEMENT revenue value | xyseries revenue PLACEMENT value | eval REVENUE=rur_rev*100/urban_rev
``````
Esteemed Legend

Mine should be far more efficient than the other answer.

Motivator

I think you're making this harder than it needs to be. Try this:

`````` sourcetype="Customer_Churn"
| stats sum(Total_Revenue) as rev by PLACEMENT
| eventstats sum(rev) as fin_rev
| eval REVENUE = rev*100/fin_rev
| table PLACEMENT REVENUE
| replace 0 with Rural in PLACEMENT
| replace 1 with Urban in PLACEMENT
``````

`stats` will separate the sums for you; there's no need to compute them separately. Once that's done, you can use `eventstats` to get the overall total, since you're just summing everything.

Contributor

wow.!!
you made it so simple in the first attempt itself.
I have same type of requirement with churn also. can you guide me in solving that. both should be in same query.
i will post a fresh one.

Motivator

I don't think you can put a `where` statement inside of `eventstats` like that. Instead, try using `eval` to create two new fields based on the value of PLACEMENT which you can then use `eventstats` on. Also, your `eval` statement for REVENUE uses a `rur_rev` field; should that be `rural_rev` instead? Something like this:

``````sourcetype="Customer_Churn"
| eval Placement0Revenue=if(PLACEMENT="0", Total_Revenue, null())
| eval Placement1Revenue=if(PLACEMENT="1", Total_Revenue, null())
| eventstats sum(Total_Revenue) as fin_rev
| eventstats sum(Placement0Revenue) as rural_rev
| eventstats sum(Placement1Revenue) as urban_rev
| eval REVENUE=rural_rev*100/urban_rev by PLACEMENT
| replace 0 with Rural in PLACEMENT
| replace 1 with Urban in PLACEMENT
``````

There may be a more elegant way of accomplishing what you need, but I think this will work.

Contributor

Hi @wpreston Yes, I have missed that. I have corrected and checked it now.
the code i have written is wrong anyways. can you please guide me in displaying the total_revenue percentage of each placement. the formula is REVENUE=rural_rev*100/fin_rev. this i have missed in above code. by this it is showing same values for both placements.