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.
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.
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
Mine should be far more efficient than the other answer.
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.
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.
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.
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.
The answer I gave above will give you what you want.
yes, i got it 🙂