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

Options
John_Whited
John_Whited Member
in Cards, Dashboards, Stories

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.

Domo.JPG

Thanks,

John Whited

Answers

  • marcel_luthi
    marcel_luthi Coach
    Options

    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
    John_Whited Member
    Options

    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
    John_Whited Member
    Options

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

    Domo, Beastmode, Average Per Day Per Month, 01292024.JPG
    Domo, Data Structure, 01292024.JPG

  • marcel_luthi
    marcel_luthi Coach
    Options

    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
    John_Whited Member
    Options

    Thanks so much Marcel!

Categories