How to Combine 2 DataSets to a Filtered Count/Aggregated DataSet with different colums
TLDR: This is a complicated dataset (for me) that I am trying to build. Basically, I am taking data from two datasets, with different columns, aggregating them to columns that is listed correctly in rows... If that makes sense. I need help going about it the right way.
Below is sample data I have and what I am trying to build from it... so you have an idea of what I am working with.
Input DataSet 1:
|3/3/2018||3/5/2018||John Doe||Closed Won|
|3/25/2018||3/27/2018||Jayne Doe||Closed Lost|
|3/16/2018||3/18/2018||John Doe||Closed Won|
|4/1/2018||4/5/2018||John Doe||Closed Won|
|4/15/2018||4/17/2018||Jayne Doe||Closed Lost|
|4/20/2018||4/21/2018||Jayne Doe||Closed Won|
Input DataSet 2:
Please note that the 'Input DataSet 2' entires listed are monthly summery stats. Each employee will have one entry for the month (the date is just listed as the 'last day of the month').
Output DataSet (What I am trying to achieve):
|Date:||Employee:||Comm Structure:||Sales:||Total Ops:||Ops Won:||Avg Op Sales:||Avg Op Sales Won:|
What I currently have setup is using a dataflow to append the rows together with all columns from both tables. I then have calculated field(s) via 'Beast Mode' to 'Count' the 'Closed Lost' and 'Closed Won' sales stages. I am having a hard time using the count function by grouping the months together, but also inserting it on the same row as the summery entry for that specific month and employee. Below is what I currently got:
CASE WHEN DATE_FORMAT(`DateB`,'%M %Y') = DATE_FORMAT(`DateC`,'%M %Y') THEN
Once I get the counts inserted correctly in the row, I can get the averages from there.
Am I going about this all wrong? I have been working on this for a couple days, but I am getting no where. Should I be filtering/agregatting the data with the options availible in the dataflow first? Any direction on how I may accomplish this would be appreciated.
- 10.7K All Categories
- 1 APAC User Group
- 12 Welcome
- 36 Domo News
- 9.6K Using Domo
- 1.9K Dataflows
- 2.4K Card Building
- 2.2K Ideas Exchange
- 1.2K Connectors
- 339 Workbench
- 252 Domo Best Practices
- 11 Domo Certification
- 461 Domo Developer
- 47 Domo Everywhere
- 101 Apps
- 705 New to Domo
- 84 Dojo
- 1.1K 日本支部
- 4 道場-日本支部へようこそ
- 23 お知らせ
- 63 Kowaza
- 296 仲間に相談
- 649 ひらめき共有