Distinct Count of Donations and Distinct Count of Donors with those Distinct Count of Donations
Essentially we would want the distinct count of donations to by the buckets and the distinct count of donors to by the Y axis. But we want to be able to do it dynamically so that we can adjust date ranges on the card level to whatever we want. So I don't know that I could do this in ETL without pushing it through each possible date range they may want to filter by. Does anyone know if this is possible to achieve at a card level? I was trying to think of a way to do it using FIXED BY and variable functions at the card level in beastmodes.
Also, the reason we use distinct counts is because a single unique donation id could have many rows due to other factors and the same with donor ids.
Here is an example, we basically want to take A1:D18 and turn it into G4:H14 and to have that update when the date range (collection date) is adjusted.
Best Answer
-
I've gotten close, but I can't get 100% there.
I am using a count of the donation_id field and then this beastmode:
(COUNT(DISTINCT
donor_id
) OVER (PARTITION BY COUNT(donation_id
)))I can't seem to figure out why that extra row is there at the bottom of the table:
I am guessing it has to do with the fact that there are 4 unique donor_id's and a row is being created for all 4.
So close, but not quite there.
If I solved your problem, please select "yes" above
1
Answers
-
Actually, this would be a better visual example
0 -
You would create 2 beastmodes and then bring them into your table card:
1. COUNT(DISTINCT
donor_id
)
2. COUNT(DISTINCTdonation_id
)If I solved your problem, please select "yes" above
0 -
If I do that, I get this:
I feel like there would maybe need to be some grouping
0 -
What are you trying to group by? It is not clear in your screenshot example. Is it donation procedure? If so just add that field to the table card next to your 2 beastmode fields.
If I solved your problem, please select "yes" above
0 -
It would be the distinct count of donation ids grouped by [date range], [acceptable procedures], then the count of distinct donors ids grouped by the above donation id counts.
So for example, if I did this in ETL, I would filter donations to the appropriate date range and acceptable donation procedures, I would put that in a Group By tile and do distinct count donation ids grouped by unique donor ids. Then I would put that in a Group By tile and group by distinct count of donation ids created in the first group by and do a distinct count donor.
So if 5 donors donated 7 times, and 3 donors donated 4 times, the X axis would have 7 and 4 and the Y would have 5 and 3. But the problem is that I want to be able to adjust the date range to anything.0 -
I've gotten close, but I can't get 100% there.
I am using a count of the donation_id field and then this beastmode:
(COUNT(DISTINCT
donor_id
) OVER (PARTITION BY COUNT(donation_id
)))I can't seem to figure out why that extra row is there at the bottom of the table:
I am guessing it has to do with the fact that there are 4 unique donor_id's and a row is being created for all 4.
So close, but not quite there.
If I solved your problem, please select "yes" above
1 -
One other approach would be to have a few common, preselected date ranges that will be used and then you can use Magic ETL to produce the groupings based on those date ranges. Users wouldn't be able to select any date range, but they'd at least have some options.
If I solved your problem, please select "yes" above
1 -
Yes, I think that might be the route we take. I didn't think this would be possible at the card level, but I was not sure so I thought I'd reach out to the Dojo. Thanks
0
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 294 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 97 SQL DataFlows
- 607 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 707 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