Population calculations in beast mode?

datamatt · September 2022

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!

ST_-Superman-_ · September 2022

@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 · September 2022

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-_ · September 2022

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 · September 2022

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 · September 2022

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-_ · September 2022

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 · September 2022

@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.

Population calculations in beast mode?

Comments

Categories