how to take previous week value to compute next week backlog

[k_harini]

Hi,
I have a requirement to calculate backlog over weeks
Backlog Current Week (Backlog1) = Current Backlog
Backlog Previous Week (Backlog2) = Backlog1+ (Open - Closed)
Backlog Previous Week (Backlog3) = Backlog2 + (Open - Closed)
current open count - current backlog
index=idx earliest=-23w latest=now | dedup ticket_number | fillnull value="Not Defined" problem_mapping system_user asset_id auto_sub_cat3 work_queue owner_name ticket_source state |itsm_ticketanalysis_to_time(time_submitted,Time,source) |search ticket_type="incident" Project="*" | bin Time span=1w|chart count(eval(State="Opened")) as open_count count(eval(State="Completed")) as closed_count by Time |eval backlog = closed_count - open_count|eval Time=strftime(relative_time(Time,"@w0"),"%d %b %y")

I tried this. but i could not grab previous week values dynamically. How to do this considering previous week? please provide any leads

[somesoni2 · Accepted solution]

Give this a try

index=idx earliest=-23w latest=now | dedup ticket_number | fillnull value="Not Defined" problem_mapping system_user asset_id auto_sub_cat3 work_queue owner_name ticket_source state |`itsm_ticketanalysis_to_time(time_submitted,Time,source)` |search ticket_type="incident" Project="*" | bin Time span=1w|chart count(eval(State="Opened")) as open_count count(eval(State="Completed")) as closed_count by Time |eval backlog = closed_count - open_count
| streamstats current=f window=1 values(backlog) as previous_backlog
| eval backlog=coalesce(previous_backlog,0)+backlog | fields - previous_backlog
|eval Time=strftime(relative_time(Time,"@w0"),"%d %b %y")

[k_harini]

This worked perfectly for my usecase.. just that i had to reverse the time. Thank you so much for your timely help.. 🙂

[alemarzu]

Hi there @k_harini

Try this please, and see if it suits you.

index=idx earliest=-23w latest=now | dedup ticket_number | fillnull value="Not Defined" problem_mapping system_user asset_id auto_sub_cat3 work_queue owner_name ticket_source state |`itsm_ticketanalysis_to_time(time_submitted,Time,source)` |search ticket_type="incident" Project="*"| eval date_var=strftime(_time,"%Y-%U") | chart count(eval(State="Opened")) as open_count count(eval(State="Completed")) as closed_count by date_var |eval backlog = closed_count - open_count

Hope it helps.

[k_harini]

Thanks for your response... But how to calculate backlog as per requirement.. Like backlog2=backlog1+closed - open.. Is this doable?

[k_harini]

can some one please help