Simple count of unique responses by location
This should be easy I think.
I have an app that produces survey response data in a pattern something like the simple example shown. Each row is a response to a question, and each response is tagged with a "ResponseGroupID" that ties them together as having been entered as part of a single "batch". I'm finding it easy to aggregate individual response values (not shown below) but I'm having trouble simply counting how many "batches" (as identified by their ResponseGroupID) each location has.
Raw Data
Location | MeasureID | ResponseGroupID | Date |
6B | 1 | 1246 | 1/1/2020 |
6B | 2 | 1246 | 1/1/2020 |
6B | 3 | 1246 | 1/1/2020 |
5LW | 1 | 8954 | 1/1/2020 |
5LW | 2 | 8954 | 1/1/2020 |
5LW | 3 | 8954 | 1/1/2020 |
6B | 1 | 3389 | 1/5/2020 |
6B | 2 | 3389 | 1/5/2020 |
6B | 3 | 3389 | 1/5/2020 |
Table I would like
Location | Distinct ResponseGroupIDs |
6B | 2 |
5LW | 1 |
Table I get
Using a mega-table and adding location and my calculated field
Location | Distinct ResponseGroupIDs |
6B | 1 |
6B | 1 |
5LW | 1 |
The formula I'm using in 'Distinct ResponseGroupIDs is
COUNT(DISTINCT `ResponseGroupID`)
Not sure if I have to create separate grouped by tables in the data flow but I'm trying to avoid additional complexity when possible and this seems as though it should be simple. Using Sum(COUNT(DISTINCT `ResponseGroupID`) in the calculated field doesn't work. Should this be done in the SQL flow that produces the model?
Comments
-
So I did achieve the results I desired in this case but not sure if this is the most efficient design pattern.
In my Data Flow, I created a new table
Select `LocationID`, `Location`, COUNT(DISTINCT `ResponseGroupID`) as 'Distinct ResponseGroupID' From `ajh_rounding_recursive_final_output` Group BY `LocationID`
Then joined this new table to the main output (shown in the second LEFT JOIN below)
SELECT rm.`Measure` AS "RoundingMeasures.Measure", rm.`Section` AS "RoundingMeasures.Section", rm.`ID` AS "RoundingMeasures.ID", rd.`MeasureID` , rd.`ID` , rd.`ResponseGroupID` , rd.`Comments` , rd.`Created` , rd.`Date` , rd.`SubmittedBy`, rd.`Location` , rd.`LocationID` , rgi.`Distinct ResponseGroupID` FROM `tbl_rounding_data` rd LEFT JOIN `ajh_rounding_measures` rm ON rd.`MeasureID` = rm.`ID` LEFT JOIN `tbl_responsegroupids_by_location` rgi ON rd.`LocationID` = rgi.`LocationID`
Then, in a data card, I can use "MAX" as an aggregation (each value is the same for every entry of the location) and it gives me what is shown below.
Table
Location Distinct ResponseGroupIDs 6B 2 5LW 1 My question is, is this this the best way to do this? If I wanted to count how many distinct ResponseGroupIDs there were by person, do I have to build this out as a separate table too (I don't mind, just want to make sure I'm not missing a much easier way to approach this)?
Thanks for reading and for your recommendations!
0 -
While this seemed to give the results I desired, there is no way to filter by any of the many dimensions so this won't really work.
Still looking for some direction on this, thanks.
0 -
Hi @PhilD ,
Are the only two fields you have listed the location and your count distinct calculated field? If that's the case then your locations might appear the same but aren't actually the same (perhaps a trailing space).
Can you try wrapping your location in a new beast mode to strip any trailing spaces?
TRIM(`Location`)
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0 -
Thanks @GrantSmith
The locations come from a table in the app so they are the same.
Funny thing, I actually created a new card from scratch and my first attempt worked (I thought that it should). I had both cards open side by side and verified everything was absolutely positively identical and the first on kept giving me the results I shared. After I recreated it fresh, the simple formula worked... not sure what was going here but I wish I could have back the hours I wasted ?
0 -
@PhilD instead of JOINING the aggregated values to your transactional data, make your dataset TALLER instead of wider.
Create a section of data where ActivityType = Actual. Then UNION your aggregation where ActivityType = GroupByResponseGroup (or whatever your appropriate group by clause is).
In your beast mode use
-- metrics in numerator
sum( CASE WHEN ActivityType = Actual Then ... ) /
--- group by in denominator
sum( CASE WHEN activity TYpe = GroupByResponseGroup Then ...)
Try to avoid count distinct by reshaping your data to have a metric column with a 1 or a 0 to represent a summable count.
If you provide a decent dataset, i can make a long-form tutorial on this b/c it is a recurring question in the Dojo.
Jae Wilson
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"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.5K Charting
- 712 Beast Mode
- 50 App Studio
- 39 Variables
- 668 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 45 Workflows
- 7 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 116 Manage
- 113 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 104 Community Announcements
- 4.8K Archive