# Averages over 5, 10, 30 days

Hey Team,

I am new to DOMO so I hope this may be something easier than i have figured out.

I want to create a line(?) chart that counts the items in one column (with an associated date field) and then provides me a moving average of those counts over the past 5 days, 10 days, and 30 days.

for example:

over the past 30 days there have been 35 incidents, of those 11 have occurred in the past 10 days, but only 3 in the past 5 days

3 numbers on a line 1.16, 1.1, 0.6

The numbers would change (slightly) every day.

Is it just a "Trend" ??

Thanks

Tim

• you could try to create 2 beast modes, 1 for calc and other for label.

Calc Beast mode :

count(case when `date` <= current_Date - 5 then `incident` end) / case when `date` <= current_Date - 5 then 5 end

Label beast mode:

case when `date` <= current_Date - 5 then 'Last 5 days' end

Note: I have only given for 5 days, you can replicate for 10, 15,30  as per your requirement.

Let me know if this works.

• Coach

Hi @tstimple ,

You can utilize the windowing function LAG to calculate the rolling average based on the number of days. Window functions are a feature switch so if you currently don't have access to them talk with your CSM about getting it turned on.

Example 5 day rolling window:

```(LAG(COUNT(`one column`), 1) OVER (ORDER BY `Date`) +
LAG(COUNT(`one column`), 2) OVER (ORDER BY `Date`) +
LAG(COUNT(`one column`), 3) OVER (ORDER BY `Date`) +
LAG(COUNT(`one column`), 4) OVER (ORDER BY `Date`) +
LAG(COUNT(`one column`), 5) OVER (ORDER BY `Date`))
/ 5```

10 day rolling window:

```(LAG(COUNT(`one column`), 1) OVER (ORDER BY `Date`) +
LAG(COUNT(`one column`), 2) OVER (ORDER BY `Date`) +
LAG(COUNT(`one column`), 3) OVER (ORDER BY `Date`) +
LAG(COUNT(`one column`), 4) OVER (ORDER BY `Date`) +
LAG(COUNT(`one column`), 5) OVER (ORDER BY `Date`) +
LAG(COUNT(`one column`), 6) OVER (ORDER BY `Date`) +
LAG(COUNT(`one column`), 7) OVER (ORDER BY `Date`) +
LAG(COUNT(`one column`), <span class="lia-unicode-emoji" title=":smiling_face_with_sunglasses:">?</span> OVER (ORDER BY `Date`) +
LAG(COUNT(`one column`), 9) OVER (ORDER BY `Date`) +
LAG(COUNT(`one column`), 10) OVER (ORDER BY `Date`))
/ 10```

And you can extrapolate for a 30 day rolling window.

@jaeW_at_Onyx has some really great tutorial videos that he's put out and has one specifically around this type of solution: https://www.youtube.com/watch?v=cnc6gMKZ9R8. I highly recommend giving that video a watch (and others on his channel). He gives great background and walks through the problem really well.