How to append a row and a column of averages?

[dzyfer]

Hi, I have a search that uses the chart command to split by 2 fields, such that the results are shown below. The data is split by Name and Month.

I would like to add a row with the average of all Names for each month, and a column with the average of all Months for each Name.
I have tried using appendpipe and appendcols for each case, but couldn't quite figure out the syntax using a chart command.

PS. Each row is already an appended subsearch.

[kamlesh_vaghela]

@dzyfer 

Can you please try this and check Avg_1 and AVG_2?

YOUR_SEARCH_TILL_CHART
| addcoltotals 
| eventstats count as row_count 
| foreach * 
    [ eval <<FIELD>>=if(isnull(Name),'<<FIELD>>'/(row_count-1),'<<FIELD>>')] 
| fields - row_count 
| addtotals 
| eval column_count=0 
| foreach * 
    [ eval column_count=column_count+1] 
| eval Avg_1 = Total/(column_count-3)
| fields - column_count Total
| eval Name=if(isnull(Name),"AVG_2",Name)

My Sample Search :

| makeresults 
| eval _raw="Name	03-22	04-22	05-22	06-22	07-22	08-22
A	11.11	11.11	11.11	11.11	11.11	11.11
B	22.22	22.22	22.22	22.22	22.22	22.22
C	33.33	33.33	33.33	33.33	33.33	33.33
D	44.44	44.44	44.44	44.44	44.44	44.44
E	55.55	55.55	55.55	55.55	55.55	55.55
" 
| multikv forceheader=1 
| table Name 0* 
| addcoltotals 
| eventstats count as row_count 
| foreach * 
    [ eval <<FIELD>>=if(isnull(Name),'<<FIELD>>'/(row_count-1),'<<FIELD>>')] 
| fields - row_count 
| addtotals 
| eval column_count=0 
| foreach * 
    [ eval column_count=column_count+1] 
| eval Avg_1 = Total/(column_count-3)
| fields - column_count Total
| eval Name=if(isnull(Name),"AVG_2",Name)

I hope this will help you.

Thanks
KV
If any of my replies help you to solve the problem Or gain knowledge, an upvote would be appreciated.

 

[dzyfer]

Hi @kamlesh_vaghela, this works great! Thanks! A quick add-on question, is there a way for row_count and column_count to ignore null values?

[kamlesh_vaghela · Accepted solution]

@dzyfer 

Can you please try this?

YOUR_SEARCH
| foreach * 
    [ eval "<<FIELD>>_row_count" = 0
        ] 
| foreach * 
    [ eval "<<FIELD>>_row_count" = if(isnull('<<FIELD>>'),'<<FIELD>>_row_count' ,'<<FIELD>>_row_count'+1)
        ] 
| addcoltotals 
| foreach * 
    [ eval <<FIELD>>=if(isnull(Name),'<<FIELD>>'/('<<FIELD>>_row_count'),'<<FIELD>>') 
        ] 
| fields - *_row_count 
| addtotals 
| eval column_count=0 
| foreach * 
    [ eval column_count=if(isnull('<<FIELD>>'),column_count ,column_count + 1)] 
| eval column_count = (column_count-3)
| eval Avg_1 = Total/column_count
| fields - column_count Total
| eval Name=if(isnull(Name),"AVG_2",Name)

My Sample Search :

| makeresults 
| eval _raw="Name	03-22	04-22	05-22	06-22	07-22	08-22
A	11.11	11.11	11.11		11.11	11.11
B	22.22	22.22	22.22	22.22	22.22	22.22
C	33.33	33.33		33.33	33.33	33.33
D	44.44	44.44	44.44	44.44	44.44	44.44
E	55.55	55.55	55.55	55.55	55.55	55.55
" 
| multikv forceheader=1 
| table Name 0* 
| foreach * 
    [ eval "<<FIELD>>_row_count" = 0
        ] 
| foreach * 
    [ eval "<<FIELD>>_row_count" = if(isnull('<<FIELD>>'),'<<FIELD>>_row_count' ,'<<FIELD>>_row_count'+1)
        ] 
| addcoltotals 
| foreach * 
    [ eval <<FIELD>>=if(isnull(Name),'<<FIELD>>'/('<<FIELD>>_row_count'),'<<FIELD>>') 
        ] 
| fields - *_row_count 
| addtotals 
| eval column_count=0 
| foreach * 
    [ eval column_count=if(isnull('<<FIELD>>'),column_count ,column_count + 1)] 
| eval column_count = (column_count-3)
| eval Avg_1 = Total/column_count
| fields - column_count Total
| eval Name=if(isnull(Name),"AVG_2",Name)

I hope this will help you.

Thanks
KV
If any of my replies help you to solve the problem Or gain knowledge, an upvote would be appreciated.

[yuanliu]

Are you thinking of addcoltotals, addtotals?

| addcoltotals
| addtotals

[dzyfer]

Hi @yuanliu , thanks for replying. As I need the averages of the data, just using addtotals or addcoltotals doesn't seem to be sufficient, as the number of rows and columns are dynamic, hence I cannot simply add the totals then divide by a fixed number of rows or columns. Unless you have a way to dynamically count the number of rows and columns?

[yuanliu]

In that case, use eventstats and foreach with addcoltotals.

| eventstats sum(*) as total_*
| foreach *
    [ eval avg_<<FIELD>> = <<FIELD>> / total<<FIELD>> ]
| fields - total_*
| addcoltotals