Ignore some filters (not all) in Beast Mode aggregation

pl_Luke
pl_Luke Member
in Magic ETL

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:

DateReasonRejected QuantityChecked Quantity
1/1/2021Dimensional510
1/1/2021 010
1/1/2021Aesthetic1010
1/2/2021Dimensional710
1/2/2021 0

10

 

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

DateRejected SUMChecked SUMRejected Rate
1/1/2021153050%
1/2/202172035%

 

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

DateRejected SUMChecked SUMRejected Rate
1/1/2021103033%
1/2/20210200%

 

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!

Comments

  • MarkSnodgrass
    MarkSnodgrass Coach

    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.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • pl_Luke
    pl_Luke Member

    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:

     

    DateRejected SUMChecked SUMRejected Rate
    1/1/20211010100%

     

    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
    MarkSnodgrass Coach

    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. 

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • pl_Luke
    pl_Luke Member

    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
    MarkSnodgrass Coach

    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.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

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

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • swagner
    swagner Contributor

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

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

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

    @swagner take a look at FIXED Functions

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Categories