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?
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!
@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.
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.