Tableau-like Parameter actions
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?
@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.Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"1
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.**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.0
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 messy0
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.**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1
- 7.6K All Categories
- 913 Connectors
- 241 Workbench
- 470 Transform
- 1.8K Magic ETL
- 60 SQL DataFlows
- 445 Datasets
- 23 Visualize
- 194 Beast Mode
- 2K Charting
- 6 Variables
- 14 Cards, Dashboards, Stories
- 1 Automate
- 348 APIs & Domo Developer
- 82 Apps
- 14 Predict
- 3 Jupyter Workspaces
- 11 R & Python Tiles
- 241 Distribute
- 59 Domo Everywhere
- 241 Scheduled Reports
- 14 Manage
- 35 Governance & Security
- 17 Product Ideas
- 1.1K Ideas Exchange
- Community Forums
- 15 Getting Started
- 1 Community Member Introductions
- 49 Community News
- 18 Event Recordings
- 579 日本支部