Splunk Search

## stats and eventstats

Contributor

Hi All.

I want to calculate the percentage of churned_customer in rural and urban areas.
The columns i have are CHURN with values 0 and 1 where "0" represents unchurn and "1" represents churned and another column PLACEMENT with values 0 and 1 where 0 represents Rural and 1 represents Urban.

the query i had used is

``````sourcetype="Customer_Churn"
| stats count(eval(CHURN="1")) as churned_count by PLACEMENT
| stats count(eval(CHURN="1")) as tot_churn_count
| eval churned_percentage=churned_count*100/tot_churn_count
| stats values(churned_percentage) by PLACEMENT
``````

it is not showing any values. please resolve it.

I have calculated for total_revenue in the same manner by using the code mention below.
here i have calculated tot_revenue percentage in rural and urban areas of all churns. where in above i want only churned. please some one help me out.

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

Santhosh.

Tags (5)
1 Solution
Motivator

After your first `stats` statement, your `CHURN` values are gone, so the second `stats` statement doesn't work. You want something similar to the second search, like so:

``````sourcetype="Customer_Churn"
| stats sum(eval(if(CHURN=="1",1,0))) as churned_count by PLACEMENT
| eventstats sum(churned_count) as tot_churn_count
| eval churned_percentage=churned_count*100/tot_churn_count
| stats values(churned_percentage) as Churn_Percent by PLACEMENT
| replace 0 with Rural in PLACEMENT
| replace 1 with Urban in PLACEMENT
``````
New Member

sourcetype="Customer_Churn"
| eventstats count(eval(CHURN="1")) as churned_count by PLACEMENT
| eventstats count(eval(CHURN="1")) as tot_churn_count
| eval churned_percentage=churned_count*100/tot_churn_count
| eventstats values(churned_percentage) by PLACEMENT

hope this will work

Motivator

After your first `stats` statement, your `CHURN` values are gone, so the second `stats` statement doesn't work. You want something similar to the second search, like so:

``````sourcetype="Customer_Churn"
| stats sum(eval(if(CHURN=="1",1,0))) as churned_count by PLACEMENT
| eventstats sum(churned_count) as tot_churn_count
| eval churned_percentage=churned_count*100/tot_churn_count
| stats values(churned_percentage) as Churn_Percent by PLACEMENT
| replace 0 with Rural in PLACEMENT
| replace 1 with Urban in PLACEMENT
``````
Contributor

Hi @aweitzm yeah.!!! I got it individually.
but, when I am combining both of the queries it is showing empty results, can you please club them.
I have tried like this.

`````` sourcetype="Customer_Churn"
| stats sum(eval(if(CHURN=="1",1,0))) as churned_count by PLACEMENT
| eventstats sum(churned_count) as tot_churn_count
| eval churned_percentage=churned_count*100/tot_churn_count
| stats sum(Total_Revenue) as rev by PLACEMENT
| eventstats sum(rev) as fin_rev
| eval REVENUE = rev*100/fin_rev
| stats values(churned_percentage) as Churned_Percent values(REVENUE) as Rev_Percent  by PLACEMENT
| replace 0 with Rural in PLACEMENT
| replace 1 with Urban in PLACEMENT
``````

Can you find any clue for that. I want them to give values when combined both queries.

Communicator

Hi Santhosh,

When you execute transformation commands you will end up loosing other columns its better you use event level stats comamnds till the final transformation to retain all the values.

Once you execute the first stats command you wille end up with only PLACEMENT, churned_count. Use below query,
sourcetype="Customer_Churn"
| eventstats sum(eval(if(CHURN=="1",1,0))) as churned_count by PLACEMENT
| eventstats sum(churned_count) as tot_churn_count
| eval churned_percentage=churned_count*100/tot_churn_count
| eventstats sum(Total_Revenue) as rev by PLACEMENT
| eventstats sum(rev) as fin_rev
| eval REVENUE = rev*100/fin_rev
| stats values(churned_percentage) as Churned_Percent values(REVENUE) as Rev_Percent by PLACEMENT
| replace 0 with Rural in PLACEMENT
| replace 1 with Urban in PLACEMENT

Contributor

Here is the Query for my actual result. Thanks for helping me @Arun_N_007 and @aweitzman

``````sourcetype="Customer_Churn"
| eventstats sum(eval(if(CHURN=="0",1,0))) as churned_count by PLACEMENT
| eventstats sum(eval(if(CHURN=="0",1,0))) as tot_churn_count
| eval churned_percentage=churned_count*100/tot_churn_count
| eventstats sum(Total_Revenue) as rev by PLACEMENT
| eventstats sum(Total_Revenue) as fin_rev
| eval REVENUE = rev*100/fin_rev
| stats values(churned_percentage) as Churned_Percent values(REVENUE) as Rev_Percent by PLACEMENT
| replace 0 with Rural in PLACEMENT
| replace 1 with Urban in PLACEMENT
``````
Contributor

@Arun_N_007 sorry .I tried to delete the comment of mailID. it has deleted all the thread. I am so sorry.
and I have accepted the answer.

Take the 2021 Splunk Career Survey

### Help us learn about how Splunk has impacted your career by taking the 2021 Splunk Career Survey.Earn \$50 in Amazon cash! Full Details! >

Get Updates on the Splunk Community!