Partition in Beastmode

Jbrorby

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

 

dojo example.xlsx

GrantSmith

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.