Last 7 days COUNT for each day

guitarhero23
guitarhero23 Contributor
in Magic ETL

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


**Make sure to like any users posts that helped you and accept the ones who solved your issue.**

Comments

  • rahul93
    rahul93 Contributor

    Hey,

    Let me know if this works.

     

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

     

    thanks

  • guitarhero23
    guitarhero23 Contributor

    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.


    **Make sure to like any users posts that helped you and accept the ones who solved your issue.**
  • rahul93
    rahul93 Contributor

    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. 

  • ST_-Superman-_
    ST_-Superman-_ Coach

    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.


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • guitarhero23
    guitarhero23 Contributor

    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.


    **Make sure to like any users posts that helped you and accept the ones who solved your issue.**
  • user09742
    user09742 Member

    Did you figure this out?

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    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.

     

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • user09742
    user09742 Member

    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.

Categories