Show data from current week and breakdown weekly for the whole year for some fields

[sumitdhameja1]

Hi,

I am a newbie to splunk so apologies if I didn't follow any right etiquettes while creating this issue.

I am trying to create a table where I can show some statistical fields(avg,95perc,count) from current week for a given host and then show the trend from the beginning of the year for only count,95perc. Something like the table below:

Service	CurrentWeek:total	CurrentWeek: avg_some_field	CurrentWeek: some_field_95	2021-03-19:total	2021-03-19: some_field_95	2021-03-12:total	2021-03-12: some_field_95
HOST1
HOST2

The way I'm currently doing this by using a join for the current week and aggregating data for the year broken down by week on host field as shown below:

    index=some_index earliest=@w latest=now sourcetype="some_src_type" 

    | stats count as total, avg(some_field) as avg_some_field,  perc95(some_field) as some_field_95 by host_name

    | join type=outer host_name

    [search index=some_index  earliest=@y latest=@w  sourcetype="some_src_type" 

     | bin _time span=1w

     | eval week=strftime(_time,"%Y-%m-%d")

     | stats count as total_by_week,  perc95(some_field) as some_field_95 by host_name, week

     | chart sum(total_by_week) as total,  mean(some_field_95) as some_field_95  by host_name, week useother=f

    ]

    | rename host_name as "Host", total as "CurrentWeek:total", avg_some_field as "CurrentWeek:avg_some_field", some_field_95 as "CurrentWeek:some_field_95" 

ASK:
If a host doesn't exist in current week, it doesn't show up in final table (because of the join) Is there a better way to solve this? Also, I would like following weeks to be in descending order in columns to show the most recent ones first. Currently it shows then in ascending order.

@woodcock @DalJeanis @niketn