Hello,
I posted this question a bit ago and didn't get a response, so I am trying again for some help. I have a handful of availability reporting widgets on a modern dashboard that report based on type of site or report overall average availability for all sites for the current and past month. When I originally implemented the widgets, they seemed to calculate averages correctly, but as time went on, they became more inaccurate. The averages are coming out much lower than their true calculations. If anyone can help identify an issue in the SWQL or share an idea as to why these KPI's are off, please let me know!
SWQL for Overall Availability:
SELECT
Round(Avg(([data].[ContainerStatus].PercentAvailability)*1.00), 3) as [Average Percent Availability]
, Case
When MONTH( DateTrunc('Month', [data].[ContainerStatus].DateTime) ) = 1 Then 'Jan'
When MONTH( DateTrunc('Month', [data].[ContainerStatus].DateTime) ) = 2 Then 'Feb'
When MONTH( DateTrunc('Month', [data].[ContainerStatus].DateTime) ) = 3 Then 'Mar'
When MONTH( DateTrunc('Month', [data].[ContainerStatus].DateTime) ) = 4 Then 'Apr'
When MONTH( DateTrunc('Month', [data].[ContainerStatus].DateTime) ) = 5 Then 'May'
When MONTH( DateTrunc('Month', [data].[ContainerStatus].DateTime) ) = 6 Then 'Jun'
When MONTH( DateTrunc('Month', [data].[ContainerStatus].DateTime) ) = 7 Then 'Jul'
When MONTH( DateTrunc('Month', [data].[ContainerStatus].DateTime) ) = 8 Then 'Aug'
When MONTH( DateTrunc('Month', [data].[ContainerStatus].DateTime) ) = 9 Then 'Sep'
When MONTH( DateTrunc('Month', [data].[ContainerStatus].DateTime) ) = 10 Then 'Oct'
When MONTH( DateTrunc('Month', [data].[ContainerStatus].DateTime) ) = 11 Then 'Nov'
When MONTH( DateTrunc('Month', [data].[ContainerStatus].DateTime) ) = 12 Then 'DEC'
Else ''
End as [Month]
FROM orion.groups AS data
WHERE
MONTHDIFF( DateTrunc('Month', [data].[ContainerStatus].DateTime), Getdate() ) < 1
SWQL for Site Availability:
SELECT
Round(Avg(([data].[ContainerStatus].PercentAvailability)*1.00), 3) as [Average Percent Availability]
, Case
When MONTH( DateTrunc('Month', [data].[ContainerStatus].DateTime) ) = 1 Then 'Jan'
When MONTH( DateTrunc('Month', [data].[ContainerStatus].DateTime) ) = 2 Then 'Feb'
When MONTH( DateTrunc('Month', [data].[ContainerStatus].DateTime) ) = 3 Then 'Mar'
When MONTH( DateTrunc('Month', [data].[ContainerStatus].DateTime) ) = 4 Then 'Apr'
When MONTH( DateTrunc('Month', [data].[ContainerStatus].DateTime) ) = 5 Then 'May'
When MONTH( DateTrunc('Month', [data].[ContainerStatus].DateTime) ) = 6 Then 'Jun'
When MONTH( DateTrunc('Month', [data].[ContainerStatus].DateTime) ) = 7 Then 'Jul'
When MONTH( DateTrunc('Month', [data].[ContainerStatus].DateTime) ) = 8 Then 'Aug'
When MONTH( DateTrunc('Month', [data].[ContainerStatus].DateTime) ) = 9 Then 'Sep'
When MONTH( DateTrunc('Month', [data].[ContainerStatus].DateTime) ) = 10 Then 'Oct'
When MONTH( DateTrunc('Month', [data].[ContainerStatus].DateTime) ) = 11 Then 'Nov'
When MONTH( DateTrunc('Month', [data].[ContainerStatus].DateTime) ) = 12 Then 'DEC'
Else ''
End as [Month]
FROM orion.groups AS data
WHERE
([data].[CustomProperties].[Business_Unit_Groups]) = ('X')
And MONTHDIFF( DateTrunc('Month', [data].[ContainerStatus].DateTime), Getdate() ) < 1