Summing column values based on dates

Reply
Blue Belt

Summing column values based on dates

I'm trying to sum values as they fall within a range of dates. I think I have to do this in Beast Mode, because the Window function in ETL doesn't seem to allow for this though I have never successfully used it. I am not proficient in MySQL.

 

I want to sum the 'EDP Visits' between Sundays. In the table below, the values for Oct 22 thru Oct 28 would be summed together, so I would end up with a row for date Oct 28 and a calculated value of 745.

 

Capture.JPG

Extra complications - I'm not always working in 7-row intervals:

-The min(date) in my tables is not always a Monday as in the example below. It can be any day of the week. So

-On SOME weeks I want to extend the summing from Monday to Monday - this occurs when there is a national holiday. I have an index of dates that can be used to identify these outlier dates. 

 

Date EDP Visits


Has anyone solved for this?

Broadway + Data
Purple Belt

Re: Summing column values based on dates

I think for hte first part Beastmode will work well. Just cerate a Beastmode "Week" and use that as the "Date' on your table. If using in a graph "Graph by week" will get this to work (although that is such an obvilous solution that i am guessign it that not apply.

 

For your indexed, nor sure what that data looks like, I would join that data to the current dataset using ETL then create a bestmode along the lines of

 

Week (Case when 'Index Cloumn' = 'Indiexed' then (Date - 6)

            else Date

           end)

 

Announcements
Win free lodging at Domopalooza! We want to hear your Domo story, enter our Dojo contest and win up to four nights lodging at Domopalooza. Click here!