Using conditional sum after case statement

[dwong2]

.....search
| eval Type=case(like(publishId,"%U"),"unsubscribed",like(publishId,"%S"),"subscribed")
| stats count by tile Type
| sort Type

How do you get a separate sum for "%U" and "%S" ???

[niketn · Accepted solution]

@dwong2, can you please try the following search with eventstats to compute Total by Type and add Total to each event for corresponding Type:

<YourBaseSearch>
| eval Type=case(like(publishId,"%U"),"unsubscribed",like(publishId,"%S"),"subscribed") 
| stats count by tile Type
| eventstats sum(count) as Total by Type
| eval Avg=round(count/Total,2)
| sort Type

Following is a run anywhere search based on sample data provided:
PS: Commands from | makeresults till | table tile Type count generate the sample data as per the question.

| makeresults
| fields - _time
| eval data="PosterTile subscribed 2;
Tile1 subscribed 4;
Tile2 subscribed 6;
Tile3 subscribed 2;
Tile4 subscribed 2;
BannerTile unsubscribed 5;
Tile1 unsubscribed 6;
Tile2 unsubscribed 5;
Tile3 unsubscribed 4;
Tile4 unsubscribed 7"
| makemv data delim=";"
| mvexpand data
| makemv data delim=" "
| eval tile=mvindex(data,0), Type=mvindex(data,1), count=mvindex(data,2)
| table tile Type count
| eventstats sum(count) as Total by Type
| eval Avg=round(count/Total,2)

[FrankVl]

A sum over what field? Please be a bit more specific in what data you have and what result you're after.

[knielsen]

You could use a different representation if that fits your needs, eg:

.....search
| eval Type=case(like(publishId,"%U"),"unsubscribed",like(publishId,"%S"),"subscribed") 
| chart count over tile Type
| addcoltotals

More in spirit of the original representation maybe:

.....search
 | eval Type=case(like(publishId,"%U"),"unsubscribed",like(publishId,"%S"),"subscribed") 
 | stats count by tile Type
 | sort Type
 | stats list(Type) as Type list(count) as Count by tile
 | eventstats sum(Count) as Total by tile

• but there must be something more elegant. 🙂

[dwong2]

This is my output now:

tile Type count
PosterTile subscribed 2
Tile1 subscribed 4
Tile2 subscribed 6
Tile3 subscribed 2
Tile4 subscribed 2
BannerTile unsubscribed 5
Tile1 unsubscribed 6
Tile2 unsubscribed 5
Tile3 unsubscribed 4
Tile4 unsubscribed 7

Can't figure out how to sum the subscribed and unsubscribed and the calculate to get an average in percentage.
i.e. for subscribed Tile1/Total tile of subscribed only so 4/16; Tile2/Total tile of subscribed only so 6/16
for unsubscribed Tile1/Total tile of unsubscribed only so 6/27; Tile2/Total tile of unsubscribed only so 5/27