How do I add Calculated Field for Average Count Per Day Per Month (Trends)?

John_Whited

How do I add Calculated Field for Average Count Per Day Per Month (Trends)

I have a card that I am currently using and I want to create a trend to tell the story of how we are doing with a particular metric at our site. The goal is display a trend line for average total number of alarms (in this case) that occur each day, per month. So, the chart below shows total alarms as a sum per month, but I need average alarms per day per month. Does that make sense? Our goal is to reduce the average daily total alarms occurrences over time.

Thanks,

John Whited

marcel_luthi

There are a couple of questions there that will change how things are calculated. You can do this with a beast mode something like:

AVG(COUNT(`AlertId`) FIXED (BY `AlertDay`))

This supposing that your dataset has individual entries per alert, but without knowing how your data is structured is a bit hard to provide more insights or suggestions.

John_Whited

Marcel,

I'd be happy to share the structure with you if you'd like.

I really appreciate your insight and help!

Thanks,

John

John_Whited

See snips here. The data structure is similar, but I don't have it the EventStampUTC broken out into individual columns, therefore I need to figure out how to use the Beastmode query / script to give me Average Per Day Per Month (or Per Week, or whatever).

marcel_luthi

Since what you have is a UTC timestamp, you'll need to apply your FIXED function on the date not the exact timestamp, you can do this by Simple wrapping the DATE function around it, like:

AVG(COUNT(`description`) FIXED (BY DATE(`EventStampUTC`)))

This assumes that all your entries will have a description field, if that might not be true then you'd need to swap this to something that is always filled out (reason why we first suggested and ID kind of column).

You will then drop this Beast Mode as the value to use in your graph, which should theoretically work. What this does is that it gives you the daily total, and that daily total is then averaged at any level above that.

John_Whited

Thanks so much Marcel!