Where clause with eval and stats

[SanthoshSreshta]

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.

Thanks a lot in advance.

[woodcock]

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

[woodcock]

Mine should be far more efficient than the other answer.

[aweitzman · Accepted solution]

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.

[SanthoshSreshta]

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.

[wpreston]

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.

[SanthoshSreshta]

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.
Can you please correct it.

[aweitzman]

The answer I gave above will give you what you want.

[SanthoshSreshta]

yes, i got it 🙂