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.
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)."%"
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).
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.