Best Of

@gbuckley , Domo does not support parameterization the way you're thinking.

You can recreate the metric you're looking for by altering your data model.

APPEND a copy of your data to the dataset, and in the second set, replace Fruit w/ "All-Fruits", then your users can filter on "Apple" OR "All-Fruits" and with that slice of data you can calculate your ratio without relying on a window function

sum(case when fruit <> 'All-Fruits' then ... ) / sum( case when fruit = 'All Fruits' then ... )

Alternatively, you can do a cartesian product of each row of the dataset for each fruit for each day. so on a day where you have 2 fruits you'd have 4 rows, on a day with 5 fruits you'd have 25 rows.

select 
a.fruit as 'fruit_ignoreMe'
c.fruit as 'fruit_report'
case when a.fruit = c.fruit then amount as numerator,
a.amount as denominator
from trans a,
JOIN (SELECT distinct fruit, date) c
on a.date = c.date

then your beast mode is sum(numerator) / sum(denominator)

use fruit_report as the filter. when you filter on "apple" you'll have a copy of EVERY row for the day, but the numerator will only be apples, and denominator will contain everything.

@user09644

you could use list-dataflows and output to a csv. then use any other scripting tool (python for example) to continue the automation process.

@MarkSnodgrass is correct, the CLI is mostly a wrapper for interacting with the APIs, the querying and automation part is the next mile which you'll pick up with Python, Node server or some other scripting environment.

here's a similar ish example using PyDomo (python sdk)

@user06979 this is the kind of thing you have to do in ETL or a Dataset View.

You cannot complete this requirement in analyzer.

The gut check response might be to use a window function, but you can't filter on aggregates without a beta feature switch AND with recent changes to Domo, even that won't work.

You could implement the window function in DSVs, but at that point you might as well take your dataset GROUP BY Customer calculate the SUM(AMOUNT) and then just JOIN it back onto your transactions.

Now you have a materialized column that you can filter on.

https://www.youtube.com/watch?v=eifSYZIcPzg

@user048760 ,

the short answer is "a card can only be powered by data from ONE dataset". In short, no you cannot create a card that spans two datasets.

What @Lady and @GrantSmith are suggesting is that you combine your two datasets into one dataset.

Be careful, instinctively, most people will try to JOIN their data together (in ETL or a Dataset View), but most days of the week that's NOT the way you want to go.

Instead try to UNION your data such that common columns are aligned.

https://www.youtube.com/watch?v=PVbOeLSae9o

Hi @user048760

in order for your card to utilize the metric is must be in your dataset. I agree with @Lady_KMac that views are a good option to combine the dataset. Alternatively is it possible for you to add a new card to your page for this metric? That might be a simpler option if you don’t want to combine the dataset together.

@MarkSnodgrass is pointing out that an empty string, '' <> NULL. hence his test:

TRIM(ID) = '' or ID is null

HOWEVER, SUM ... 1 else 0 END will give you COUNT not COUNT (DISTINCT).

@ekola ,

you should validate whether COUNT DISTINCT is including NULLS or not, because standard behavior in many SQL implementations is to IGNORE NULLS with COUNT DISTINCT. But again... NULL <> ''

https://bertwagner.com/posts/count-distinct-and-nulls/