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

neilprobst
neilprobst 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

Best Answers

  • marcel_luthi
    marcel_luthi 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:

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

  • marcel_luthi
    marcel_luthi 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

  • MichelleH
    MichelleH Coach

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

    image.png

  • neilprobst
    neilprobst 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

  • marcel_luthi
    marcel_luthi 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:

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

  • neilprobst
    neilprobst 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

  • marcel_luthi
    marcel_luthi 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

Categories