Adding indentifier field to stats output

[gyphawk]

I have an index: base_data

The index has data added on a weekly basis. I would like to identify the
instances of field 'Ref' present in the previous week, but not present now (therefore they
have been removed); and to identify instances of 'Ref' that are present now but not the previous
week (therefore they are new this week).

I have managed a list of these previous and current 'Ref' values by using the following:

index=base_data earliest=-5d@w1 latest=@w6 | dedup Ref | table Ref| append [search
index=base_data earliest=@w0 | dedup Ref | table Ref] | stats count by Ref| where count < 2

Which returns a table with one column with a single instance of each Ref value that meets the
criteria.

Ref
ABC1
ABC2
ABC3

However this does not tell me which rule / dataset the Ref has been identified in. My question is -
how do I modify the search to add in a field to identify this?

For example:

index=base_data earliest=-5d@w1 latest=@w6 would be considered PreviousWeek

index=base_data earliest=@w0 would be considered CurrentWeek

to return something along the lines of

Ref | Source
ABC1 | PreviousWeek
ABC2 | PreviousWeek
ABC3 | CurrentWeek

To identify which criteria of previous/current the Ref has come from.

Many thanks in advance

[somesoni2 · Accepted solution]

Give this a try

index=base_data earliest=-1w@w1 latest=now 
| eval period=if(_time<relative_time(now(),"@w0"),"LastWeek","ThisWeek")
| chart count over Ref by period
| eval result=case(LastWeek=0 AND ThisWeek>0,"Newly Added", LastWeek>0 AND ThisWeek=0,"Removed", true(),"")

[gyphawk]

That works a treat thank you.