Last 7 days COUNT for each day

Goal: I want a total COUNT of rows for the last 7 days, for each day. Meaning, that I have Ex.  a line graph where the point for 5/22 includes the previous 7 days (5/15 - 5/21) and the point for 5/21 would be the total of 5/14-5/20 and so on.

 

Context: I append data every 15 minutes to a dataset that shows the current users online at that time. I'm trying to report every day on the # of users for the last 7 days. I'm trying to brainstorm if I can do this in a beastmode before going the route of a dataflow.

 

Sample Data:

Dojo 1.JPG

Comments

  • rahul93
    rahul93 NY 🟠

    Hey,

    Let me know if this works.

     

    sum(case when `activedatetime` >= current_date()  - 7 then 1 else 0 end)

     

    thanks

  • Thanks for the response,

     

    something like that is what I was originally going for but realized it's not going to work. 

     

    I would want to look back on say 5/1 and see what the sum of online users was for the previous 7 days to that, and so forth for any day of any month. using current_date only allows me to have a number that shows the last 7 days from current day.

  • rahul93
    rahul93 NY 🟠

    Ah my apologies i thought you wanted the last 7 days from today. I believe you wont be able to do this in a beast mode with the existing fields. Since, the data loads every 15 minutes i dont believe creating a dataflow would be an issue unless the dataset isnt too large. 

  • I agree.  I think you will need to engineer this field within a dataflow.  Are you always going to want a running 7 day total?

     

    Is this already part of an ETL or MySQL dataflow?

     

    If you want help manipulating the dataflow, please share the input file structure and any steps already in place.

  • Thanks guys, I figured I was hoping too much from a beast mode in this case but I've seen some crazy ones so wanted to check before going to a dataflow. I'll attempt to do this and make progress in a dataflow before asking for you to do work to help me.

  • Did you figure this out?

  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟤

    https://www.youtube.com/watch?v=cnc6gMKZ9R8
    You could use the LAG() window function in beast modes (you may need to ask your CSM to enable the feature switch).

     

    Alternatively, see if you can get a hold of the Domo Data View beta, it can be optimized for performance which may be necessary given that your data is updating frequently.

     

  • Thank you. Glad this is not in ETL. And thanks for creating a channel on YouTube. I have been waiting for someone to this this for a while.