Looking for Solution to Inaccurate Widget Averages

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

  • For greater visibility among those who know SWQL, I've moved this to the Orion SDK forum.  This is the best place to post SWQL inquiries.

  • FYI, I don't see any error in your queries - they seem to be returning what you are requesting.  Do you have another report that is showing you different numbers?  If so, can you reply to this thread and post the pictures?

  • Is it possible that you need to include the year in the query? i.e you may be seeing values for multiple years when calculating the specific month. 

  • Yes, for example I have the second query I listed reporting a overall type availability of 99.978%. But, the table that is reporting on each individual site for this type looks like this:

    100
    100
    100
    98.117
    100
    100
    100
    100
    100
    99.962
    100
    100
    99.981
    94.396
    100
    99.708

    The true average is 99.510%. I am not sure where the misread comes in. Below, Tony mentioned maybe year is a factor and I will have to look into that too

  • That could be, how would I need to tweak the SWQL to read only for 2021 or current year? I am not much of a SWQL expert

  • I don't have immediate access to enough historical data to test right now, but if you could validate this may work for Last Month. 

    datetime < DATETRUNC('Month',GETDATE()) AND datetime > ADDMONTH(-1,DATETRUNC('month',GETDATE()))
    and this for the current month
    WHERE datetime > ADDMONTH(-1,DATETRUNC('month',GETDATE()))
  • Sorry, where exactly would I paste that into my query above? 

  • This should replace everything after the WHERE clause in your query i.e lines 21, 21 & 22 in your examples to become 

    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].[ContainerStatus].DateTime < DATETRUNC('Month',GETDATE()) AND [data].[ContainerStatus].DateTime > ADDMONTH(-1,DATETRUNC('month',GETDATE()))

  • Hi Tony, thank you for the response! Running your query returned an error for not recognizing "datetime" in the where clause. How do I work around this?

  • My bad, 100% user error and not checking what I had pasted. The updated query should work well.