Add getDayOfYear array as a filter in "if else" check - help with syntax

[AlanFoley]

Hi All

I am working worth a service metric - in this case Response Time - where I want to split by Business Hours (BH) and Non Business Hours (NBH) - this works fine as I also include Sat and Sat into NBH

The next step I want to do is include a list of public holidays to be included in NBH by providing an "Array" containing a list of these days as per "getDayOfYear" format to match against

In the code below my attempt at this is commented out

I need some help with the correct syntax - am I on the right track?

Apprecaite the help

### DQL ###

timeseries {AvgResponseTime=avg(dt.service.request.response_time,default:0),e=end()},interval:1h,by:{dt.entity.service}
// ,from:"2024-04-01T00:00:00" ,to:"2024-04-30T23:59:59" // Select the month
| filter dt.entity.service == "SERVICE-xxx"
| fieldsAdd AvgResponseTime_BH = if( getHour(e[])>6 and getHour(e[])<19 and getDayOfWeek(e[]) <6, AvgResponseTime[])
,AvgResponseTime_NBH = if( getHour(e[])<7 or getHour(e[])>18, AvgResponseTime[]
,else:if(getDayOfWeek(e[]) >5, AvgResponseTime[] //Include Sat and Sun
// Need to Include Public Holiday's to Non Business Hours [all 24 hours]
// ,else:if(getDayOfYear(e[]) in(e[],array,("1","81","89","92","118","122","168","222","268","351","360","361")), AvgResponseTime[]
))
// Temp fields to verify Monthly Hour totals for Business Hours and Non Business Hours
| fieldsAdd HoursInMonth = arraySize(arrayRemoveNulls(AvgResponseTime))
,Hours_BH = arraySize(arrayRemoveNulls(AvgResponseTime_BH))
,Hours_NBH = arraySize(arrayRemoveNulls(AvgResponseTime_NBH))
| fieldsAdd Hours_Check_MustBeZero = HoursInMonth - (Hours_BH + Hours_NBH)
| fieldsRemove AvgResponseTime,AvgResponseTime_BH,AvgResponseTime_NBH

[krzysztof_hoja · Accepted solution]

I would do it this way:

| fieldsAdd bh = getHour(e[])>6 and getHour(e[])<19 and getDayOfWeek(e[]) <6 and not in(getDayOfYear(e[]), array(1,81,89,92,118,122,168,222,268,351,360,361))
| fieldsAdd 
    AvgResponseTime_BH = if( bh[], AvgResponseTime[]),
    AvgResponseTime_NBH = if( not bh[], AvgResponseTime[] )
| fieldsAdd 
    HoursInMonth = arraySize(arrayRemoveNulls(AvgResponseTime)),
    Hours_BH = arraySize(arrayRemoveNulls(AvgResponseTime_BH)),
    Hours_NBH = arraySize(arrayRemoveNulls(AvgResponseTime_NBH))
| fieldsAdd Hours_Check_MustBeZero = HoursInMonth - (Hours_BH + Hours_NBH)
| fieldsRemove AvgResponseTime,AvgResponseTime_BH,AvgResponseTime_NBH, bh

[AlanFoley]

Hi krzysztof_hoja

Works great 

Appreciate your time

Thanks a mil

Alan

Preview