Tableau-like Parameter actions

gbuckley

I am trying to allow a user to filter a chart that uses an aggregation. My data looks like this:

I want a user to be able to choose 'Apple' and then the ensuing line chart would show percentage of items each day that were apple (40% over 1/1, 30% over 1/2, and 60% over 1/3).

In tableau, I would set up a parameter (p_fruit) containing all values in the 'Fruit' column. I would then set up an aggregation something like this:

sum( case when 'Fruit' = p_fruit then 'Items' else 0) / sum('Items')

Is it possible to recreate this type of functionality in Domo?

jaeW_at_Onyx

@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.

MarkSnodgrass

Are you familiar with drill paths in Domo? This will do what you are wanting to do and doesn't require any parameter passing as Domo takes care of that for you. You can watch this Domo video for a quick tutorial on it: https://www.domo.com/help-center/videos/watch/eB3jtaWAvAs

You could also design your page as a dashboard and configure the interaction filters to allow one card to affect other cards on the page.

gbuckley

Hi Mark, I don't think that drill paths or card interactions address my issue. Both of those techniques filter the underlying data driving the card. For example, I build a simple bar chart for fruit:

Then I build a drill path so that clicking on the apple bar takes me to the line chart. But since the underlying data is filtered to show only rows with "Apple" in the 'Fruit' column, the percentage will always be 100%

The same will be true for card interactions. I want to use a parameter-like thing to select certain rows for the aggregation, but NOT filter the whole dataset.

This was the beast mode I used for the line chart:

Partitioning by 'Date' (the finest detail in the dataset) allows the user to choose their own timeframe and the calculation should still work. If 'Fruit' is passed as the Series, we get the line chart with the proper percentages. But I only want to show one line at a time (the fruit the user chooses) since in my actual use case we have 9 'fruits' so the chart gets quite messy

GrantSmith

Hi @gbuckley

you can control which lines are shown after aggregation has applied by clicking on the legend. So you could hide all the other fruits except apples and allow the user to select which fruit they want to display on the chart.

Alternatively you could pre aggregate your data in an ETL and calculate your overall percentage based on the total using the rank and window tile in a magic Etl. Then when the user filters the data it would have the percentage for the entire dataset still and wouldn’t be affected by the filtering but only show your filtered fruit.

jaeW_at_Onyx

@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.