Population calculations in beast mode?

datamatt · 2022-09-23T16:18:40+00:00

There was an error rendering this rich post.

So, I've got a dashboard in which I'm trying to show the population over time at a facility for one of my cards. So, x-axis should be a date, and y-axis should be how many people were present at the facility that date. The dataset we have has a row for each client, and two of the columns are date admitted and date discharged (along with a bunch of other standard demographic stuff, age, gender, etc.).

So, I know how to do this just fine in python (and at the moment, that's how I've done it for the dashboard, via a python script in domo's magic ETL). However, this means we can't filter by any of the demographic information in the other columns, because the population numbers get hard coded so to speak in the ETL phase. (For what it's worth, the way I've done it in python is make a new dataframe where each row is a day, and a column is a head count on that day).

Any thoughts on how something like this could be done in Beast Mode? Or one of Domo's other tools? Just anything that would allow us to still be able to apply demographic filters after the fact. I'm still somewhat new to Domo, so if there's a tool I've overlooked that would be perfect for this, that's definitely a valid answer.

I think that's everything, but please ask me to clarify or give more info if needed. Thanks a bunch!

Comments

ST_Superman

@datamatt

I think that I am following this question correctly. I have taken a dataset that looks like this:

And converted it so that I get one row of data for each date between the DateAdmitted and DateDischarged for each ClientID. For example, my output dataset (filtered for Client "B") has 5 rows of data. Each row representing 1 HeadCount for the given day:

I can now graph the total headcount and still be able to filter for Gender or Age or Facility:

What is happening to the metadata in your python script that is preventing you from filtering on that data?

datamatt

Thanks @ST_-Superman-_ , you've understood it exactly correctly. I'll have to sheepishly admit that what was happening to the metadata had to do with the fact that I was only creating one row for each date, not each date per client id. So each row got a headcount, but instead of 1, it was the whole facility headcount for that day (e.g. 572), which as it is can't be separated back into its constituent demographic components.

One record per date per client would push my dataframe to about 600k records (and it would only continue to grow into the future, by about 400k per year)—I can't remember in what situations and at what thresholds Domo starts giving you "Not all data is shown" messages on cards—but best I can see, your solution is spot on, and one of the bigger reasons I hadn't thought of it is because there were too many wrong trees (Beast Mode and the like) for me to be barking up. Many thanks!

ST_Superman

The other option would be to group them, but to group them by each field that you want to be able to filter by... so you would get 1 row for each day and each combination of facility, gender, age, etc. This should reduce the row count a bit.

In your example of 572, you might end up with 20 rows of data that add up to 572, but that would be better than 572 rows.

datamatt

That's another great idea. This time, I had actually thought of this one 🙂 At least until now, I'd been opting to not go this route, because I'd been unsure which categories I wanted to expose to the eventual dashboard user(s) for filtering. Of course I could just pre-emptively make all of them available to be exposed, but I'd put this option lower on the priority list. In any case, thanks again for the excellent ideas 🙂

datamatt

So here's another conundrum: do we suppose this should be possible to do if we're graphing by week? Best I can tell, the answer's no, but this would not be the first time I thought something wasn't possible in Domo but it was.

To graph by day is far too granular for my particular card (and it does indeed run into Domo's "Warning: Not all data shown"), but graphing by month isn't granular enough, so it has to be by week.

The issue that I'm running into when trying to graph by week is that getting the daily headcount involves an aggregation (summing all the 1's), but grouping by week also involves an aggregation as well (I'd want the average headcount of the seven days for any given week).

So if it's by day, I can either use Beast Mode—SUM('headcount')—or just select the sum aggregation on 'headcount' directly in the analyzer's graph controls. So that works great. But, weekly, the best I can think to do is AVG(SUM('headcount')) in Beast Mode (which never results in a graph, but that non-specific "something went wrong" message). I would want to use a combination of SUM('headcount') in Beast Mode and then to select the AVG aggregation in the analyzer, but having an aggregation in Beast Mode disables them in the analyzer.

I suppose I could try out @ST_-Superman-_'s second suggestion (hard code each filter combination), but it's 13 things I want to be able to filter by, which creates some 6.7k combinations (actually, thinking about it, I might be underestimating that by a fair bit). I guess the real issue with that is not the number of combinations but that it's not clear to me how I'd connect them up to a slicer or other filter such that from the user perspective, it just seems like a normal filtering situation.

So yes, do we think this should be possible? Or have I run into a legitimate limitation. Thanks again!

ST_Superman

AVG(SUM(`headcount`)) FIXED (BY `date`)

That will give you the average daily headcount. You would want to put Date field as your x axis and select the "min" aggregation. Then sort the data by WEEK(`date`)

Or you could use your "graph by" option to change it to week. I prefer using the MIN of the Date field though because it is more readable in my opinion to see the first date of the week rather then Week 15 or Week 26... I don't really know what dates those are at a glance.

jaeW_at_Onyx

@ST_-Superman-_ and @datamatt , typically for anything like headcount data, you'd want data the granularity of one row per client_id per facility per day.

if you don't, imagine i have a customer who was admitted last month and stays for 3 months. in my report, i would only show them on the first day they were admitted.

you are correct it would lead to row growth to have one row per user per day, but it is definitely necessary.

to prevent the "not showing all rows" warning you need to make sure you have a metric / aggregate function. I usually add something like sum(1) as to give me rowcount, or you could add a binary column to the dataset.

Quick Links

Find more posts tagged with

Filters

Other Categories

Product Ideas
Have a Domo product enhancement idea? Submit or upvote on ideas in the Ideas Exchange.
Ideas Exchange
Suggest & vote on new features you would like to see implemented in the Domo Product.
Data Connections
Ask questions about Connectors, Workbench, Cloud Amplifier and get best practices from Domo peers
Connectors
Connectors, Custom Connectors, Writeback
Workbench
Ask questions about Workbench, a secure, client-side solution for uploading your on-premise data to Domo.
Cloud Integrations
Ask questions about Cloud Integrations and Federated Data connection to your data warehouse or lake.
Data & ETL
Ask questions about Magic ETL, SQL DataFlows, DataFusion, Dataset Views and get best practices from Domo peers
Magic ETL
Ask Magic ETL questions and get answers from Domo peers
SQL DataFlows
Ask SQL DataFlow questions and get answers from Domo peers
Datasets
Ask DataFusion and Dataset Views questions and get answers from Domo peers
Visualize & Apps
Ask questions about Beast Mode, Cards, Charting, Dashboards, Stories, Variables and get best practices from Domo peers
Dashboards
Ask Cards, Dashboards, and Stories questions and get answers from Domo peers
App Studio
Ask questions about building apps in App Studio.
Pro-code Components
Ask questions about pro-code components and Domo Bricks and get answers from Domo peers.
Charting & Analyzer
Ask questions about charting and Analyzer and get answers from Domo peers.
Calculations & Variables (Beast Mode)
Ask questions about using calculated fields and Variables (Beast Modes) in Analyzer.
AI & Data science
Ask questions about DomoAI and get answers from Domo peers.
Domo AI & AI Chat
Ask questions about AI Chat and AI assistants.
Managing AI
Ask questions about managing AI with AI Playground, AI projects, AI models, and more.
Jupyter Workspaces
Ask questions about Jupyter Workspaces, Notebooks, and file share.
Automate
Ask questions about App Framework, Workflows, Domo Bricks, Domo Developer, API and get best practices from Domo peers
Workflows
Ask questions about Task Center, building automations with Domo Workflows, and executing JavaScript or Python code with Code Engine.
Alerts
Ask questions about managing alerts in Domo and get answers from Domo peers.
Distribute
Ask questions about Domo Everywhere, Scheduled Reports, Mobile and get best practices from Domo peers
Domo Everywhere
Ask questions about embedded analytics with Domo Everywhere.
Reporting
Ask questions about Scheduled Reports, Report Builder, and Slideshow Publications.
Manage
Ask questions about Governance Administration, Approvals, Teams, Alerts, and Buzz and get best practices from Domo peers
Governance & Security
Ask questions about People, Groups, Roles, Sandbox, Activity log, Buzz, Teams, Approvals and PDP and get best practices from Domo peers
Navigation & Productivity
Ask questions about navigation, Projects & Tasks, Goals, and Buzz chat.
APIs
Ask APIs and Developer.domo.com questions and get answers from Domo peers
Add-ins & Plugins
Ask questions about plugins, Microsoft add-ins, and other third-party software integrations.
Domo Community Gallery
Watch how our Customers are using Domo to solve their complex problems.
Product Releases
Domo support and product teams are here to live-answer questions about the most recent product releases. Please post questions in this Forum board for all users to benefit (rather than submitting a support ticket).
Domo University
Questions or discussions related to Domo University, trainings and certifications
Community Forums
Getting Started
Welcome to Domo's Community Forums! You'll find everything you need to get started in this category.
Community Announcements
Get the latest from Domo's Community Team.
Social Groups
Archive
Old or outdated content that could still be found helpful.