Can I even do this with an IF?

[edenzler]

Hi, multi value field called OverallStatus - states are On Track, Marginal, Critical. Another field ID, contains a unique value to count.

| stats count(ID) AS Event count(eval(OverallStatus="On Track")) AS OnTrack count(eval(OverallStatus="Marginal")) AS Marginal count(eval(OverallStatus="Critical")) AS Red by Project ID

Here is the metric: ( basically a Red - Yellow - Green )

Green = 80% or more projects are green and none are Red. Yellow = 70% - 79% projects are green or 1 – 20% are Red. Red = under 70% projects are green or over 20% are Red.

I just can't get my head around the "IF" ... Really only need to show this back in a Red Yellow or Green.

Any help or direction, would be greatly appreciated!

Cheers...

[lguinn2]

Try this

yoursearchhere
| stats count(ID) AS Event count(eval(OverallStatus="On Track")) AS OnTrack count(eval(OverallStatus="Marginal")) AS 
    Marginal count(eval(OverallStatus="Critical")) AS Critical by ProjectID
| eval percentOnTrack = OnTrack * 100 / (OnTrack + Marginal + Critical)
| eval percentMarginal = Marginal * 100 / (OnTrack + Marginal + Critical)
| eval percentCritical = Critical * 100 / (OnTrack + Marginal + Critical)
| eval Status = case(percentOnTrack > 79 AND percentCritical = 0, "Green",
                     percentOnTrack > 69 AND percentOnTrack < 80, "Yellow",
                     percentCritical > 0 AND percentCritical < 21, "Yellow",
                     percentOnTrack < 70,"Red",
                     percentCritical > 20,"Red",
                     1=1,"Unknown")
| table ProjectID Status percentOnTrack percentMarginal percentCritical OnTrack Marginal Critical

You could do it with an if, but I just think it is easier with a case function.

[lguinn2]

I don't know which one is faster, I just like the case function because I find it easier to read and debug!

You could try them both and look at the search job inspector for the run time... that's not perfectly accurate because search load varies moment to moment - but if there is a big difference you will see it. (Search job inspector = the box with the "i" in case you didn't know)

[edenzler]

Thanks Iguinn! I managed to do it this way, wondering which one would be better?

| stats count(ID) AS Event count(eval(OverallStatus="On Track")) AS Green count(eval(OverallStatus="Marginal")) AS Yellow count(eval(OverallStatus="Critical")) AS Red by Product
| eval Green1=Green / Event * 100
| eval Yellow1=Yellow / Event * 100
| eval Red1=Red / Event * 100
| table Product Event Red Yellow Green Red1 Yellow1 Green1 | eval "Overall Status" = if (Green1 <= 70 OR Red1 >= 20, "Red", if (Green1 >= 70 AND Green1 <= 79.9 AND Red1 <= 20, "Yellow", if (Green1 >= 80 AND Red <= 0, "Green","Unknown")))

Cheers!