Filter a table with aggregated rows on a dashboard with a filter card
Hello,
I have table on a dashboard that is displaying aggregated rows based on on a larger data set. The table has two columns 'parent company' and 'spend', with spend being the sum of all spend at the parent company level.
I want to add a range selector filter to the dashboard to filter the rows of the table based on the aggregated 'spend' column. But when I filter on 'spend' it filters the rows in the underlying dataset based on those values rather than the aggregated rows in the table.
So if Parent Company 1 is the parent company for Company A and Company B and Company A has spend of $100 and Company B has $300 in the dataset, the table with have one row for Parent Company 1 with a spend = to $400. If I set my filter to exclude spend under $200, the filter will remove Company A and the table with now display Parent Company 1 = $300, but I want to filter on the aggregated value (Parent Company level) so the table row remains displayed and spend = $400.
Let me know if you more clarification or visuals
Best Answers
-
You should be able to achieve this by using a Variable (most likely 2) to enter the range start and end, and then building a BeastMode which you'll use for filtering. If your Variables are called
RangeStart
andRangeEnd
, then your BeastMode would look something like:CASE WHEN SUM(`Spend`) >= `RangeStart` AND SUM(`Spend`) <= `RangeEnd` THEN 'true' ELSE 'false' END
And you'll add that filter criteria using the equals to 'true'.
1 -
Sadly (or at least AFAIK), while the icon for the Slider control icon depicts two dots, which would make you think you can select a range with it, this is not currently the case and you need to create a different variable for the lower and higher end of the range individually. Perhaps something worth sharing in Ideas Exchange.
1
Answers
-
@neilprobst Do you have an aggregation set on the Spend filter in analyzer?
0 -
@MichelleH In this case, the filter is not in set up within the card with the table, but rather a separate filter card tied to the same dataset and displayed on the same dashboard. I didn't try to doing it with a dynamic filter within the card until now, and it appears to work when I select the 'Sum' aggregation. That solution will work but my preference is to use a separate filter card in the same dashboard rather than a dynamic filter in the card
0 -
You should be able to achieve this by using a Variable (most likely 2) to enter the range start and end, and then building a BeastMode which you'll use for filtering. If your Variables are called
RangeStart
andRangeEnd
, then your BeastMode would look something like:CASE WHEN SUM(`Spend`) >= `RangeStart` AND SUM(`Spend`) <= `RangeEnd` THEN 'true' ELSE 'false' END
And you'll add that filter criteria using the equals to 'true'.
1 -
Thanks for you help. I didn't realize I had the ability to add a variable control directly to a dashboard in order to spend. I set it up with two controls as fill-in textboxes. This solution will work, though my original desire was to have a single slider control for selecting the start and end of the range. I am assuming that it is not possible to configure a slider when the min is connected to one variable and the max to another, but I wanted to confirm
0 -
Sadly (or at least AFAIK), while the icon for the Slider control icon depicts two dots, which would make you think you can select a range with it, this is not currently the case and you need to create a different variable for the lower and higher end of the range individually. Perhaps something worth sharing in Ideas Exchange.
1
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 292 Workbench
- 4 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 95 SQL DataFlows
- 600 Datasets
- 2.1K Magic ETL
- 3.7K Visualize
- 2.4K Charting
- 685 Beast Mode
- 43 App Studio
- 38 Variables
- 655 Automate
- 170 Apps
- 438 APIs & Domo Developer
- 42 Workflows
- 5 DomoAI
- 32 Predict
- 12 Jupyter Workspaces
- 20 R & Python Tiles
- 384 Distribute
- 110 Domo Everywhere
- 268 Scheduled Reports
- 6 Software Integrations
- 111 Manage
- 108 Governance & Security
- 8 Domo University
- 25 Product Releases
- Community Forums
- 39 Getting Started
- 29 Community Member Introductions
- 98 Community Announcements
- Domo Community Gallery
- 4.8K Archive