Last 7 days COUNT for each day

guitarhero23

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:

rahul93

Hey,

Let me know if this works.

 

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

 

thanks

guitarhero23

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

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

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.

guitarhero23

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.

user09742

Did you figure this out?

jaeW_at_Onyx

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.

 

user09742

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.