How to use two stats commands with different where clauses?

[ahogbin]

Hello,

I am trying to use the stats command with 2 different where clauses with the end result being to use the 2 values to provide the total of scenario 1 displayed as a number and a percentage of scenario 2.

For example I have a total of 50 referrals (where clause 1) and a total of 100 quotes (where clause 2). The table would display 50 in one column and 50% in the other (reflecting half of the total quotes were referred.

My stats component of the search is

...| where acc="Inc" AND Stage = "Quote" AND processStatus="ManualRatingRequired" | stats count AS "Referrals" | appendcols [where acc="Inc" AND Stage = "Quote" | stats count AS tQuotes]

The first part is working and returns the expected number but the second (appendcols) returns 0. If I switch it around then the first part returns the expected total, but not the number of referrals.

I have tried various methods, but none seem to be achieving what I am trying to achieve.

Any pointers will be greatly appreciated and will go along way to saving my sanity.

Cheers,

Alastair

[sundareshr · Accepted solution]

Try this, without a sub-search

where acc="Inc" AND Stage = "Quote"  | stats count AS Quotes count(eval(processStatus="ManualRatingRequired")) as Referrals | eval perc=round(Referrals/Quotes*100, 1)."%"

[ahogbin]

Perfect.. thank you so much

[ahogbin]

One other issue I am trying to figure out in relation to the above is that each quote number can repeat multiple times which in turn is producing skewed results.

If I run a search with out the duplicates being removed I get a a total of 344 over a given time period with 69 referrals yet if I remove duplicates I end up with 101 unique events with 33 referrals over the same time period.

This obviously returns very different % values.

Is there away of amending the above to return a distinct count of the total number of quotes (duplicates removed) and then the distinct count of the referred quotes (again with duplicated removed).

Cheers,

Alastair

[ahogbin]

I have managed to get part way there with

where acc="Inc" AND Stage = "NewBusiness" | stats dc(quoteNumber) AS Quotes  count(eval(processStatus="ManualRatingRequired")) as Referrals | eval perc=round(Referrals/Quotes*100, 1)."%"

But the referrals are still being counted more than once. If I set count to dc for referrals then I , obviously, end up with only one event.