How to search count by only business days?

[lewisgrantevans]

Hello Splunk Ninjas,

I have created an 'aging' field that counts the number of days since a certain date & time. I would like to remove weekends from this count.

I am using:

| eval Created=strptime(Created,"%d/%m/%Y %H:%M")
| eval c_time=strftime(Created,"%d/%m/%Y %H:%M")
| eval aging=round((now()-strptime(c_time, "%d/%m/%Y %H:%M"))/86400,2)

Many thanks in advance.

[lewisgrantevans]

Hi @FrankVl & @VatsalJagani , I think we're close but it's not quite calculating correctly. I've attached a screenshot of my results in excel.

I used almost the exact query (I just changed the eval to make a Created2 field so i could retain the Created field).

The results for June 4th & 5th are more accurate than the excel answer, taking the time into consideration, however the results for June 6th, 7th & 10th are not accurate. You'll notice a jump in noOfBusinessDays between the 5th & 6th.

| eval Created2=strptime(Created,"%d/%m/%Y %H:%M")
| eval today = now()
| eval createdW = strftime(Created2,"%w")
| convert num(createdW)
| eval createdW = if(createdW == 0, 7, createdW)
| eval todayW = strftime(today,"%w")
| convert num(todayW)
| eval todayW = if(todayW == 0, 7, todayW)
| eval totalDays = round(((today-Created2)/(86400)),2)
| eval noOfBusinessDays = totalDays - 2 * (totalDays/7)
| eval noOfBusinessDays = if(totalDays % 7 != 0, case(createdW = 7, noOfBusinessDays-1, todayW = 7, noOfBusinessDays-1, todayW < createdW, noOfBusinessDays-2, 1=1, noOfBusinessDays), noOfBusinessDays)

Thanks again!
Lewis

[FrankVl]

Try adding a floor to the second to last line: | eval noOfBusinessDays = totalDays - 2 * floor((totalDays/7))

You don't want to take the decimal part of totalDays/7 into account.

[FrankVl]

Also: it seems to be missing logic for dealing with createdW=6 or todayW=6, right?

Ah, the stackoverflow solution that was referred to uses a function that calculates the 'days between'. And round(((today-Created2)/(86400)),2) is not the same as days between. Same for excel, that does not do a days between but adds 1.

I think you also need to replace the round with a floor: | eval totalDays = floor(((today-Created2)/86400)).

Alternative would be to subtract 2 for sundays and subtract 1 for saturdays in the case statement.

[VatsalJagani · Accepted solution]

@lewisgrantevans,

I've tried to implement Ronald's second solution. Please try.

| eval Created2=floor(relative_time(strptime(Created,"%d/%m/%Y %H:%M"),"@d"))
| eval today = floor(relative_time(now(),"@d"))
| eval createdW = strftime(Created2,"%w")
| convert num(createdW)
| eval createdW = if(createdW == 0, 7, createdW)
| eval todayW = strftime(today,"%w")
| convert num(todayW)
| eval todayW = if(todayW == 0, 7, todayW)
| eval totalDays = (today-Created2)/86400
| eval Aging = totalDays - 2 * floor((totalDays/7))
| eval Aging = if(totalDays % 7 != 0, case(createdW = 7, Aging-1, todayW = 7, Aging-1, todayW < createdW, Aging-2, 1=1, Aging), Aging)

Hope this helps!!!!

[lewisgrantevans]

Thank you @FrankVl & @VatsalJagani !

This works perfectly when taking @FrankVl last comment into consideration

"Try adding a floor to the second to last line: | eval noOfBusinessDays = totalDays - 2 * floor((totalDays/7))

You don't want to take the decimal part of totalDays/7 into account."

[FrankVl]

Careful it seems to be missing logic for dealing with createdW=6 or todayW=6, right?

The stackoverflow solution that was referred to uses a function that calculates the 'days between'. And round(((today-Created2)/(86400)),2) is not the same as days between. Same for excel, that does not do a days between but adds 1.

I think you also need to replace the round with a floor: | eval totalDays = floor(((today-Created2)/86400)) . (but then you do have an off-by-one compared to Excel, depends a bit on what you want. Is today-today equal to 0 or to 1?)

Alternative would be to subtract 2 for sundays and subtract 1 for saturdays in the case statement.

[FrankVl]

Looking a bit closer, I think the issue might actually be with the totalDays % 7 != 0. totalDays % 7 is practically never 0 except when the timestamps are identical. You need a floor around that.

So the last 2 lines should be:

| eval noOfBusinessDays = totalDays - 2 * floor((totalDays/7))
| eval noOfBusinessDays = if(floor(totalDays % 7) != 0, case(createdW = 7, noOfBusinessDays-1, todayW = 7, noOfBusinessDays-1, todayW < createdW, noOfBusinessDays-2, 1=1, noOfBusinessDays), noOfBusinessDays)

[lewisgrantevans]

Yes i was a little hasty, i noticed a miscalculation on June 5th but your most recent comment seems to have rectified it. I'm now using -

| eval Created2=strptime(Created,"%d/%m/%Y %H:%M")
| eval today = now()
| eval createdW = strftime(Created2,"%w")
| convert num(createdW)
| eval createdW = if(createdW == 0, 7, createdW)
| eval todayW = strftime(today,"%w")
| convert num(todayW)
| eval todayW = if(todayW == 0, 7, todayW)
| eval totalDays = floor(((today-Created2)/86400))
| eval Aging = totalDays - 2 * floor((totalDays/7))
| eval Aging = if(totalDays % 7 != 0, case(createdW = 7, Aging-1, todayW = 7, Aging-1, todayW < createdW, Aging-2, 1=1, Aging), Aging)

Cheers buddy

[FrankVl]

The way the totalDays is calculated is just really tricky. It might be much better to simply reduce each date to the start of that day first. That way, if the two dates are the same day of week, the totalDays is 0 or a multiple of 7. Using floor does not solve that if now() is earlier in the day than Created2.

So I would suggest:

| eval Created2=floor(relative_time(strptime(Created,"%d/%m/%Y %H:%M"),"@d"))
| eval today = floor(relative_time(now(),"@d"))
| eval createdW = strftime(Created2,"%w")
| convert num(createdW)
| eval createdW = if(createdW == 0, 7, createdW)
| eval todayW = strftime(today,"%w")
| convert num(todayW)
| eval todayW = if(todayW == 0, 7, todayW)
| eval totalDays = (today-Created2)/86400
| eval Aging = totalDays - 2 * floor((totalDays/7))
| eval Aging = if(totalDays % 7 != 0, case(createdW = 7, Aging-1, todayW = 7, Aging-1, todayW < createdW, Aging-2, 1=1, Aging), Aging)

[VatsalJagani]

Thanks @FrankVl for correction in logic. Thanks @lewisgrantevans now I've updated my answer so other user also can easily find the solution.

I think this is very common day to day life logic problem, isn't it?

[lewisgrantevans]

Amazing, thank you guys. It's now more accurate than the excel calculation. Much appreciated!

[lewisgrantevans]

Thanks guys, but i should have made it clear that i'm only using a .csv file for this query so i don't have any time fields except for the one i've created below -

| eval Created=strptime(Created,"%d/%m/%Y %H:%M")
| eval c_time=strftime(Created,"%d/%m/%Y %H:%M")
| eval aging=round((now()-strptime(c_time, "%d/%m/%Y %H:%M"))/86400,2)

Cheers,
Lewis

[FrankVl]

The solutions referring to date_wday are indeed not of any use to you. But the answer from @VatsalJagani should work, I think.

[kmorris_splunk]

You could do something like this in the beginning of your search:

[BASE SEARCH HERE]
| eval daytype=if(date_wday in ("monday","tuesday","wednesday","thursday","friday"), "busday","weekend") 
| where daytype="busday"