Skip to content

    • Cards, Dashboards, Stories

    Cards, Dashboards, Stories

    Filter a table with aggregated rows on a dashboard with a filter card

    Member
    edited September 2023 in Cards, Dashboards, Stories

    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

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Best Answers

    • Coach
      edited September 2023 Answer ✓

      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 and RangeEnd, then your BeastMode would look something like:

      1. 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'.

    • Coach
      Answer ✓

      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.

      image.png

    Answers

    • Coach

      @neilprobst Do you have an aggregation set on the Spend filter in analyzer?

      image.png
    • Member

      @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

    • Coach
      edited September 2023 Answer ✓

      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 and RangeEnd, then your BeastMode would look something like:

      1. 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'.

    • Member

      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

    • Coach
      Answer ✓

      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.

      image.png

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Quick Links

    Categories