Joining two data sets on a monthly aggregate
I am looking to combine two datasets one is a sales volume the other is an instance where we receive a claim. These are done on the day level in my data sets and I want to be able to compare them by monthly aggregates so that I can compare sales volume vs claims by month. When I try to join these two sets in an ETL it creates duplicates or omits data. So is there a way perhaps in SUMO to have the months summed up by a certain location or customer then join them?
Best Answer

In Excel there is a function, "EOMONTH", that does the same thing. So you could modify your excel file with a new column that includes that value.
I know you had mentioned magicETL in the beginning. Unfortunately there is not an equivalent function in magicETL, but if you did a magicETL / excel combination you would add the new column in your excel sheet. Then in MagicETL, group by the end of month and sum your totals before the joins.
Of course, being Domo we like to do as much as possible automatically in the product. That would involve the dataflow option discussed, but I realize dataflows do require basic SQL. In this case it would be pretty simple to write.
I work for Domo.
**Say "Thanks" by clicking the thumbs up in the post that helped you.
**Please mark the post that solves your problem as "Accepted Solution"1
Answers

Hi  The best way to accomplish this is to use the last_day function in a mysql data. See below for how this would work based on the following data:
Date Amount
Jan 1 10
Jan 2 5
Jan 3 10
If you write the following statement it will group all the data to the last day of the month:
select
last_day(Date) as 'Month End Date'
,sum(Amount) as 'Month Total'
from table
group by
last_day(Date)
This would yield the following result
Month End Date Month Total
Jan 31 25
Now sometimes there are situations where you are running on a fiscal calendar. It becomes a little more complicated, but not much. The concept is similar.
I work for Domo.
**Say "Thanks" by clicking the thumbs up in the post that helped you.
**Please mark the post that solves your problem as "Accepted Solution"1 
Thanks for your reply. These two data sets come from excel is there a way to use this function from datasets coming from excel?
Thank you!
1 
In Excel there is a function, "EOMONTH", that does the same thing. So you could modify your excel file with a new column that includes that value.
I know you had mentioned magicETL in the beginning. Unfortunately there is not an equivalent function in magicETL, but if you did a magicETL / excel combination you would add the new column in your excel sheet. Then in MagicETL, group by the end of month and sum your totals before the joins.
Of course, being Domo we like to do as much as possible automatically in the product. That would involve the dataflow option discussed, but I realize dataflows do require basic SQL. In this case it would be pretty simple to write.
I work for Domo.
**Say "Thanks" by clicking the thumbs up in the post that helped you.
**Please mark the post that solves your problem as "Accepted Solution"1 
I am still new to domo so I did not know I could do a MySql with my uploaded data set. I got it figured out with mysql thank you so much for your help!
0
Categories
 All Categories
 1.7K Product Ideas
 1.7K Ideas Exchange
 1.5K Connect
 1.2K Connectors
 295 Workbench
 6 Cloud Amplifier
 8 Federated
 2.8K Transform
 97 SQL DataFlows
 608 Datasets
 2.1K Magic ETL
 3.8K Visualize
 2.4K Charting
 709 Beast Mode
 49 App Studio
 39 Variables
 667 Automate
 170 Apps
 446 APIs & Domo Developer
 44 Workflows
 7 DomoAI
 33 Predict
 13 Jupyter Workspaces
 20 R & Python Tiles
 391 Distribute
 111 Domo Everywhere
 274 Scheduled Reports
 6 Software Integrations
 115 Manage
 112 Governance & Security
 Domo Community Gallery
 31 Product Releases
 9 Domo University
 5.3K Community Forums
 40 Getting Started
 30 Community Member Introductions
 103 Community Announcements
 4.8K Archive