Filter by Section of Dataset (Bonus point question!)

jmmc

Hi Everyone, this is a bit tricky.

There is a need to have an aggregate table that shows counts, sums, and averages for different sections of the same data set. But subsets of these sections are defined by matching filters on the dashboard.

I know that doesn't make much sense, so here is an example of a simple table.

Section Name Counts
section A 10
section B 24

The challenge is the filtered subsets of data for Section A's and B's counts will be carved out by the same filters: (Region, Store, Product, etc).

So I will have one set of filters for section A and one set of filters for section B. The filters should apply to the same data set, just different sections. Essentially, the data will be stacked twice and labeled for its section.

More specifically, the challenge is that I select a region say "Texas", I want that filter to only exclude rows from Section A, and then I want a matching region filter for section B, where I can select say "California"


And the need to have things in one dataset is presented by the table view.

Ashleigh

@jmmc I think Fixed Functions might be useful in your case. You can set it up to where only certain fields can be filtered. https://domo-support.domo.com/s/article/4408174643607?language=en_US

DavidChurchman

It might help if you flesh out your example a bit more. Based on what I understand, it sounds like you could create two table cards for your dashboard and two filter cards, and then you could edit the interactions on the filter cards using the "change interactions" menu to select which filter applies to which table card. https://domo-support.domo.com/s/article/360043428433?language=en_US

Ashleigh

@jmmc I think Fixed Functions might be useful in your case. You can set it up to where only certain fields can be filtered. https://domo-support.domo.com/s/article/4408174643607?language=en_US

jmmc

@Ashleigh thank you for the input. I think this definitely helps, but I'm not sure how I could apply different filters to the rows that are calculated in the Fixed aggregate and the rows that are calculated in another aggregate.

The fixed function seems to let you calculate aggregates from two different fields on the same row, not select different subsets of data from two different aggregates on the same row.

DavidChurchman

It might help if you flesh out your example a bit more. Based on what I understand, it sounds like you could create two table cards for your dashboard and two filter cards, and then you could edit the interactions on the filter cards using the "change interactions" menu to select which filter applies to which table card. https://domo-support.domo.com/s/article/360043428433?language=en_US

jmmc

@DavidChurchman thank you for the input, that suggestion might well be what is done.

But it is not quite what I had in mind. Here's a full mock up.

So you can see that there will be one input data set with different sections. The challenge is to be able to carve out a subsection of each section and then calculate percent increases between those subsets in a single card.

The highlighted rows from each section represent filters for that section. In section A, the data has been filtered down to just rows for Texas, and so on and so forth for each section.

Then in the output table, the sum of price for filtered subset of section A is compared to the corresponding filtered subset of each additional section to calculate percent increases in the Price column.

This strikes me as really difficult to do. The Fixed function solves the issue of being able to place values from different rows on the same row in a card, but I don't think it's flexible enough to apply to filters.

DavidChurchman

You could create a new column that concatenates "Section" and "State" and filter based on that, using the "Fixed" calculations to calculate percent increase. CONCAT(Section, State). Then you can filter on "A-Texas", "B-North Carolina", and "C-Kansas".

jmmc

@DavidChurchman this is a great idea, thank you. I believe with these two approaches, the idea can be accomplished!