Partition in Beastmode
I'm hoping someone may be able to help me understand what I am doing wrong with my beastmode logic, or if what I am trying to do is just not possible in beast mode.
I have done rank and windows in ETL before, but I am trying to avoid creating a separate dataset.
I'm wanting to filter my card to locations, by day, that had 10 or less registered donations. Each donation is given a completely unique number called a donation id. By necessity, there are multiple rows with the same donation id in our data. So, normally when I want a count of donations, so as not to double count the same value, I just use the count (distinct donation id) beast mode. But, since I am wanting to narrow down to locations with 10 or less donations ids, and since you cannot put the distinct count in a filter, I was hoping to accomplish this with a type of rank/partition concept. I've had success with this type of scenario using Rank/Window in ETL in the past, but I was hoping to accomplish this same concept in a beastmode. I know that the Partition in the beastmode is not supported by DOMO, so maybe this isnt possible.
Here is my beastmode:
rank( `lte_donation_general.donation_id`) over (partition by `lte_donation_general.collection_date`,`lte_tbl_calendar.location_name_tzw`,`lte_donation_general.donation_id`)
What it is doing for me, though, is that it is counting the occurences of each donation id per date/location combination
I have attached an example
Best Answer
-
Hi @HashBrowns
Try this (I don't have your exact data set to test so may not exactly work):
SUM(COUNT(DISTINCT `lte_donation_general.donation_id`)) over (partition by `lte_donation_general.collection_date`,`lte_tbl_calendar.location_name_tzw`)
You're essentially trying to calculate the number of unique donations for each date and location.
Window functions / partitions are supported in Domo it just requires a feature switch. Talk to your CSM.
In terms of filtering you won't be able to because by default Domo doesn't support filtering based on aggregate functions (i.e. the COUNT in your beast mode). You could talk with your CSM about an alpha feature you might be able to get turned on in your instance to filter aggregate functions but again, it's an alpha feature and may not always work as intended.
If you don't want to go that route you could always pre-aggregate your data via an ETL and then filter the counts on the cards after processing.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0
Answers
-
sorry, I provided the wrong beastmode. count is used in replace of rank, the first word in my beastmode above
0 -
Hi @HashBrowns
Try this (I don't have your exact data set to test so may not exactly work):
SUM(COUNT(DISTINCT `lte_donation_general.donation_id`)) over (partition by `lte_donation_general.collection_date`,`lte_tbl_calendar.location_name_tzw`)
You're essentially trying to calculate the number of unique donations for each date and location.
Window functions / partitions are supported in Domo it just requires a feature switch. Talk to your CSM.
In terms of filtering you won't be able to because by default Domo doesn't support filtering based on aggregate functions (i.e. the COUNT in your beast mode). You could talk with your CSM about an alpha feature you might be able to get turned on in your instance to filter aggregate functions but again, it's an alpha feature and may not always work as intended.
If you don't want to go that route you could always pre-aggregate your data via an ETL and then filter the counts on the cards after processing.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 297 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 616 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 54 App Studio
- 40 Variables
- 678 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 46 Workflows
- 8 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 32 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive