How can I calculate a relative value?

[slr]

Hi there.

I'm trying to do something like this:

Relative Conversion = Event Conversion / Total Conversion

Where:

Event Conversion is the conversion within the event (actually calculated by an eval)
Total Conversion is the sum of all conversion fields in all the events (one by event)

I can't get it through and I think that I forget something for sure. I tried append, appendcol, foreach, accum... but I can't get it.

By now, my search is something like this:

index=someindex sourcetype=somesourcetype
| stats count(eval(if_OK=="Success")) as TC, sum(field1) as field1, count as TM by Hour,CITY
| join Hour,CITY [ search index=someindex sourcetype="anothersourcetype"
    | stats count as TV by Hour,CITY ] 
| eval EventConversion=round((TV*100)/TC,2)
| eval EventQuality=round((TC*100)/TM,2) 

Some clue to this confused folk, please?

Regards.

[niketn · Accepted solution]

If the following query is working fine use eventstats command to calculate the total and append to each event.

index=someindex sourcetype=somesourcetype
| stats count(eval(if_OK=="Success")) as TC, sum(field1) as field1, count as TM by Hour,CITY
| join Hour,CITY [ search index=someindex sourcetype="anothersourcetype"
| stats count as TV by Hour,CITY ]
| eval EventConversion=round((TV*100)/TC,2)
| eventstats sum(EventConversion) as TotalConversion
| eval EventQuality=round((TC*100)/TM,2)
| eval RelativeConversion=round(EventConversion/TotalConversion,2)

[slr]

Thanks @niketnilay!

I found the trick 2 minutes ago. With this, I'm can get what I looking for.

Thanks a lot 🙂

[gcusello]

Hi slr,
to debug your search you should express single values if_OK, field1, TV, TC and TM, so you could understand if the problem is in field calculation or in eval commands.
Probably problem is in joun command.
Bye.
Giuseppe

[slr]

Hi @cusello

At the moment, the query is ok. I get the expected values. My problem is the next step: with that query, how can I calculate the Total and then use it to calculate the relative?

I can calculate the totals with addcoltotals, or even a appendpipe like this:

 appendpipe [stats sum(EventConversion) as TotalConversion]

but then, how can I put that value in the equation

Relative Conversion = Event Conversion / Total Conversion

for all the cities?