Ignore some filters (not all) in Beast Mode aggregation

pl_Luke

Hello, I have been searching around for a similar posting, but have not been able to find one describing the same question/issue.

 

I would like to calculate a SUM of a column while ignoring select filters applied to the dataset. The column needs to be affected by all filters except for a couple columns that contain multiple values I want to filter on. Specifically, if I filter on a column called "Reason" that has many distinct values I want to still be able to know the "unfiltered" total aggregation of another column.

 

For example if this is my dataset of manufactured parts that are checked and potentially rejected:

Date	Reason	Rejected Quantity	Checked Quantity
1/1/2021	Dimensional	5	10
1/1/2021		0	10
1/1/2021	Aesthetic	10	10
1/2/2021	Dimensional	7	10
1/2/2021		0	10

 

I would like the unfiltered calculation to result in (no surprises here):

Date	Rejected SUM	Checked SUM	Rejected Rate
1/1/2021	15	30	50%
1/2/2021	7	20	35%

 

And if filtering the dataset to include only the "aesthetic" reason, the "checked sum" should remain unfiltered, resulting in:

Date	Rejected SUM	Checked SUM	Rejected Rate
1/1/2021	10	30	33%
1/2/2021	0	20	0%

 

This is something I will want to do frequently, so creating one-off dataflows is not ideal. I am new to Domo, but this was fairly simple to implement in Power BI using the "ALLEXCEPT" function in a calculation statement. Please let me know if more information is needed to help explain/clarify!

MarkSnodgrass

You can do this very easily at the card level and not need to do anything at the dataflow level, which will give your user the most flexibility. For example, create a table and drag in the date, rejected quantity, and checked quantity into your column list and change the aggregation to sum for both of the quantity columns. Drag the reason into the filters section and turn on quick filters. The user can then check and uncheck which reasons they want to see totals for and it will sum them by date.

pl_Luke

Mark, thank you for the reply, however this does not address the issue I have. When creating simple sums for each column there is no issue as you say, however I want one of the columns to ignore a specific filter. In what you describe all columns would be filtered, and I do not end up with the example table that I describe I would like, instead I would get:

 

Date	Rejected SUM	Checked SUM	Rejected Rate
1/1/2021	10	10	100%

 

Which is easy to follow why it gets filtered in this way, but is not the desired result. Please let me know if further explanation of what I am asking is needed!

MarkSnodgrass

Ah, sorry, I didn't catch that nuance in your example. I'm not sure how dynamic you need it, but you could create a beast mode that sums the rejected quantity based on which reasons you want to look at. It would look like this: 

CASE WHEN 'Reason' IN (<your list>) THEN 'Rejected Quantity' 
ELSE 0
END

You would then drag this field into your card instead of the original rejected quantity column and use the sum aggregate as before. 

pl_Luke

The intent is to have it much more dynamic than listing it out manually in the calculation, however your idea inspired me a bit. Would I be able to reference a list disconnected from the dataset? Then I could use the disconnected list as my "filter", which wouldnt filter down the dataset (allowing me to get my desired totals), but in a calculation I could include it only if 'Reason' was a part of that separate list.

 

In looking around elsewhere, it sounds like you cant have multiple data sources in a card, but perhaps there is another way to get the intended result?

MarkSnodgrass

You can combine multiple datasets in a dataflow (Magic ETL) which would create a single output dataset that you would use for the card. You could certainly have a dataset that has the list of reasons you want to include and then do a left join to your main dataset to develop your include/exclude list and create your totals in the ETL.

jaeW_at_Onyx

@pl_Luke Domo are working on a beta feature that will give you this idea of a 'baseline group' that is unaffected by a specific (set of) filters.  In your case the Reason Code. 

 

this feature is in beta and I'm completely unsure when it's going to release for GA or how stable the implementation is, but definitely check in with your CSM about it.

cc @GrantSmith 

 

What you can do in the immeidate term is APPEND a copy of your data where the ReasonCode is set to "BaseLine" that way you can create a BaselineMetrice (CASE WHEN reason code = 'baseline' ... ) and then all your other metrics are CASE WHEN reasonCode <> 'Baseline' AND ... ) 

 

then from the user experience perspective they always include Baseline, and they can select or deselect other reason codes.

swagner

@jaeW_at_Onyx found this post today, do you know if the feature you mentioned got released to production?

jaeW_at_Onyx

https://domo-support.domo.com/s/article/4408174643607?language=en_US

@swagner take a look at FIXED Functions