Last 7 days COUNT for each day

Reply
Highlighted
Major Brown Belt

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



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

Re: Last 7 days COUNT for each day

Hey,

Let me know if this works.

 

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

 

thanks

Highlighted
Major Brown Belt

Re: Last 7 days COUNT for each day

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.**
Highlighted
Green Belt

Re: Last 7 days COUNT for each day

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. 

Highlighted
Black Belt

Re: Last 7 days COUNT for each day

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
______________________________________________________________________________________________
Highlighted
Major Brown Belt

Re: Last 7 days COUNT for each day

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.**
Highlighted
Yellow Belt

Re: Last 7 days COUNT for each day

Did you figure this out?

Highlighted
Red Belt

Re: Last 7 days COUNT for each day

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 heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"
Technical Success Manager Jae Wilson shares how to create lag functions in Card #Analyzer using #BeastModes https://dojo.domo.com/t5/Beast-Mode-ETL-Dataflow/...
Highlighted
Yellow Belt

Re: Last 7 days COUNT for each day

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.

Announcements
Coming Soon: Weekly Virtual User Meet-ups to answer all your questions! Stay tuned for more details to come this week.