Best Of

Hi @user000253

I'd recommend using an APPEND tile in an ETL or a DataFusion to UNION your two datasets together. However one caveat to this would be they need to be in the same format / have the same columns

You could process your data to look like the following:

| Job ID | Country | Year | Amount |

| 123 | UK | 2009 | NULL |

| 234 | UK | 2009 | NULL |

| NULL | UK | 2009 | 1000 |

Then in your card you can define two different beast modes:

Job Count:

COUNT(`Job ID`)

Revenue:

SUM(`Amount`)

Then use those two beast modes for your two different values (line and bar)

Hi @b_rad

There is an Alpha feature where you can filter on aggregate functions within Domo but it doesn't always function as expected. You can talk with your CSM about possibly enabling the Aggregate Filtering feature but it's still in an Alpha state so proceed with care.

Looking around in the Dojo, this has been a feature that has been requested in the past, but has not been implemented yet. It may be worth submitting a new idea in the ideas exchange section of the Dojo and see if it can get included.

There is a feature called Window Functions in Beast Mode that you can ask your CSM to enable that can allow for rank and window functions to be written. However, it is still not going to get you quite there. I was able to write the following in a beast to produce a rank:

RANK() OVER (PARTITION BY `dateofevent` ORDER BY COUNT(`claimnumber`) DESC)

However, when I tried to wrap it in a CASE statement so that I could drop it in a filter and exclude records with a rank higher than 10, I received a message that aggregated filters are not supported. The CASE statement looked like this.

CASE WHEN 
RANK() OVER (PARTITION BY `dateofevent` ORDER BY COUNT(`claimnumber`) DESC) <= 10 THEN 'Include'
ELSE 'Exclude'
END

Since you stated that you can't do this in the ETL because you need to allow for different time periods to be used, you might need to consider a different way to visualize the data. A scatter plot card can hold a lot of series fairly effectively.

The super secret workaround we all use...

Right outer join instead of left.  

This keeps people who don't know what they are doing from breaking stuff, but enables the rest of us to go on blissfully joining.  

It'll seem awkward at first, but in a bit, you won't even have to think about it.  

You want to set it up to pull all records from the table on the right, and only those which match from the left.  Simple reverse of what you're used to.  

Because of how fast/easy ETL flows are, I prefer to use them for most flows. My problem is that sometimes I hit the following error, "Error joining data. The left input cannot include over 10,000 duplicates. Please switch your inputs, group your data, or remove duplicates before joining". The Dojo suggests that this error is only shown to stop users from accidentely making cartesian joins, but I know that's not what I'm doing. For example, if I want to join an item table onto a transaction table, then I have to do it in SQL because I'll get the error. This leads me to having multiple flows for the same end result.

I suggest you give admins the option to disable the error.