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

Parents
  • 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. 

  • 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.

  • That worked great! Thank you very much. Looks like numbers are reporting more accurately now. Just for my knowledge, what about this query pulls in for the current year versus what I had before? 

  • Your initial query was looking for all dates for a particular month i.e give me ALL the Januarys you have. The change takes uses the dates as a range and asks for everything less than the current month and greater than the last month.

Reply Children
No Data