MySQL query for YTD Running Total
I'm having trouble creating a mySQL query that does a running total by a certain Group By dimensions.
Here is a sample structure of the data:
The YTD Total column is what I'm trying to calculate. The raw data is essentially the first 4 columns. I need to create a Running Total of visits for the year by STORE and CHANNEL.
Appreciate anyone's help!! Thanks!
We have the Redshift SQL enabled in our instance and are able to use this SQL line to calculate a running total:
sum(paymentamount) over(partition by ClaimNumber order by dateofeventyear,all_monthsofdev rows unbounded preceding)
I'm not seeing this exact syntax available available in MySQL, so maybe you can get Redshift enabled if it isn't already.
Are the Running Total cards which would eliminate the need to do any SQL not working for you?
https://knowledge.domo.com/Visualize/Adding_Cards_to_Domo/KPI_Cards/Building_Each_Chart_Type/Running_Total_Grouped_Bar_Graph**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.0
Thanks for your response. No I don't have Redshift, nor have the option to enable it. I want to do a pre-calculation due to some cards that require it (such as the gauge cards). Otherwise, I would just use the Running Total cards.
If anyone else has any ideas, much appreciated. Thanks!0
@user06643 - You can utilize windowing functions within a beast mode calculation. For example:
sum(`visits`) over(partition by `channel`, `store` order by `date`)
MySQL has supported windowing functions since version 8.0 so this should work in MySQL as well if you're at or above that version. If not then you'd need to utilize variables within MySQL to keep track and then reset the values when your store or channel changes which is quite a bit more complex.**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0
Domo does not have MySQL v8, so you actually won't find support for windowed functions in MySQL dataflows
You can do it using variables
THIS IS THE FAR EASIER SOLUTION!
Slight tweak to @GrantSmith 's beast mode sample.
sum(sum(`visits`)) over(partition by `channel`, `store` order by `date`)
I didn't expect to reshare this video so many times, but you can do what you want to do via windowed functions in Beaast Modes.
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"1
- 10.5K All Categories
- 8 Connect
- 918 Connectors
- 250 Workbench
- 472 Transform
- 1.7K Magic ETL
- 69 SQL DataFlows
- 477 Datasets
- 198 Visualize
- 254 Beast Mode
- 2.1K Charting
- 11 Variables
- 82 Cards, Dashboards, Stories
- 17 Automate
- 354 APIs & Domo Developer
- 89 Apps
- 3 Workflows
- 20 Predict
- 5 Jupyter Workspaces
- 15 R & Python Tiles
- 247 Distribute
- 63 Domo Everywhere
- 243 Scheduled Reports
- 21 Manage
- 42 Governance & Security
- 178 Product Ideas
- 1.2K Ideas Exchange
- 12 Community Forums
- 27 Getting Started
- 14 Community Member Introductions
- 55 Community News
- 4.5K Archive