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.
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)
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)
Thanks @niketnilay!
I found the trick 2 minutes ago. With this, I'm can get what I looking for.
Thanks a lot 🙂
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
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?