FIXED functions are difficult to understand and should be rolled into WINDOW functions

jaeW_at_Onyx
jaeW_at_Onyx Coach
edited December 2023 in Beast Mode Ideas


the FIXED function KB, https://domohelp.domo.com/hc/en-us/articles/4408174643607-Beast-Mode-FIXED-Functions, fails to differentiate it's functionality to WINDOW functions (despite using similar language).

By borrowing from WINDOW function syntax FIXED functions mislead users as to what’s happening under the covers making it difficult to adopt.

This looks like functionality designed to mirror Tableau, but

https://help.tableau.com/current/pro/desktop/en-us/calculations_calculatedfields_lod_overview.htm


The KB examples duplicate known and well understood classic Window Function examples but fails to illuminate how Adrenaline is behaving differently under the covers.


SUM(SUM(1) FIXED () ) can be written as SUM(SUM(1)) OVER () … why the duplicate functionality?Β 

Β 

Are they arriving at the same result the same way or is there something different about the query execution that retrieves the result?


SUM(SUM(1) FIXED ( BY Region)) can be written as SUM(SUM(1)) OVER (PARTITION BY Region)

Fixed introduces PARTITIOINING without calling it PARTITIONING. Why?

  1. Does FIXED (BY) operate the same way as OVER (PARTITION BY)Β -- looks like it.
  2. It is deeply confusing to see the β€œBY” recycled without some other word in front.
  3. In Window functions we have ORDER BY and PARTITION BY
  4. It might be appropriate (and familiar) to just keep the language of PARTITION BY if it accomplishes the same action OR clearly articulate why they are not the same in the KB.
  5. Some might argue that there’s a precedence for multiple function implementations that have overlapping functionality ex. DATE_ADD and DATE_SUB.Β I’d argue that those are well-documented functions and easily understood outside of Domo whereas the concept of FIXED is net new, and the documentation and implied functionality is vague.


MAX(SUM(Total SALES) FIXED (ADD City))Β Is the first net new piece of functionality.

  1. The ability to subdivide data on a column that is not displayed on the chart is a huge departure from current functionality in Analyzer and Window functions, this is a great addition. – frankly this should just be rolled into WINDOW Function functionality.
  2. Instead of FIXED(), I would prefer to see ADD added to the set of WINDOW() function parameters.
  3. Ideally MAX(SUM(total sales)) OVER (ADD City) would accomplish the same thing while recycling our understanding of the WINDOW functions implementation in Domo.
  4. I would recommend the language of INCLUDE / EXCLUDE instead of ADD / REMOVE because add might imply a mathematical expression (especially if you’re in the context of writing expressions).


AVG(AVG(UnitPrice) FIXED (REMOVE ProductCategory)) reads as β€œthe average of an average” which is typically regarded as β€˜bad math’ and is NOT what FIXED functions are actually doing.

  1. This is the heart of my frustration with the implementation / description of FIXED.Β Β Β FIXED sounds like it’s intended to be calculated before aggregation (the GROUP BY clause), but my ideal state would be to have a version of Window functions that did not require the double aggregation.


WINDOW = AVG(AVG(score)) OVER() = 3.85

  1. While this is not the result we typically β€˜want’, it is mathematically correct (the average of 3 averages)


FIXED = AVG(AVG(score) FIXED () ) = 3.87

  1. While this is the result we β€˜want’, the syntax reads as the β€œaverage of an average”.Β 
  2. The math performed appears to be SUM(allRows) / COUNT(allRows) which should be represented as AVG(score) OVER ()


FIXED functions are misleading

  1. SUM(AVG(score) FIXED () ) and MAX(AVG( score ) FIXED()) yield the same result.


Again --- this is my frustration with FIXED functions.Β It is not intuitive and does not align with a SQL developer’s understanding of WINDOW functions despite borrowing heavily from that vocabulary.Β If it’s something different – use different syntax or be VERY explicit in the documentation.


The REMOVE parameter is AMAZING – but should just be rolled into WINDOW functions

  • Just like the ALTER tile in Magic ETL allowed me to DROP columns instead of SELECT, the REMOVE parameter allows me to choose the columns I want to exclude from my Partition Window (instead of explicitly enumerating the ones I want to partition by).Β This makes Window functions more flexible in user-guided analysis because they can alter the axis of a chart without having to rewrite all the WINDOWed beast modes.
  • Instead of a FIXED function, I’d rather see REMOVE added to the language of the OVER () clause.Β 
  • Ideally, I’d rather see INCLUDE / EXCLUDE instead of ADD/REMOVE because add implies mathematical expression (add vs. subtract).


The addition of FILTER NONE and FILTER ALLOW <field> is amazing – but would rather see in a WINDOW function.

  • For recycled language I’d prefer to see FILTER NONE / INCLUDE / EXCLUDEΒ instead of FILTER NONE / ALLOW / DENY

In conclusion, I do believe the FIXED function introduces net new functionality that would be a huge benefit to WINDOW functions.Β Given the quality of the explanations provided in the KB, the lack of clarity on what’s actually happening with FIXED functions, and the misleading syntax, I believe it would cause more damage than harm to share this feature with users who have any exposure to SQL.

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"
16
16 votes

In Review Β· Last Updated

Comments

  • 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"
  • Carstond
    Carstond Domo Employee

    Thanks for the feedback @jaeW_at_Onyx. I have submitted this to our product team. I will keep you posted on any changes that may align with your feedback.

  • @jaeW_at_Onyx Thank you for posting this, you hit the nail on the head. I was initially very excited about the introduction of FIXED functions, but can honestly say I have not tried to use them because of the time involved in learning what appears to just be new syntax for window functions. As a SQL user, I find the FIXED definitions to be ambiguous compared to seemingly equivalent elements of a window function.

  • @MichelleH -- yeah ... so they were devised as parity for a Tableau feature (level of detail expressions). which ... ironically if you read THAT documentation it helps Domo make a little more sense.

    I guess Domo is issuing multiple SQL queries instead of just one query (a'la the Segments feature). which is objectively very cool from the technology standpoint as well as the possibilities it opens up.

    i just think it's a bummer that it was explained in a way that is so obtuse :(

    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"
  • Don't forget that you can use FIXED to allow the calculation to ignore filtered fields as well. I don't believe that is doable with partitioning. I do think we could all benefit from more understanding around what FIXED is compared to WINDOW functions and when/why to use one over the other.


    β€œThere is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • FILTER ALLOW/DENY is the most useful piece of FIXED for me. What would juice up its usefulness to the next level is an ability to use static filters in the function.

    For example -

    SUM(SUM(`totalSales`) FIXED (FILTER `Client`='ABC Company'))

    Rather than simply allowing or denying the field filters to affect the calculation, you could dictate the filter state directly in the function. This would replicate what Microsoft/Power BI did with CALCULATE(SUM(`totalSales`),FILTER(`Client`="ABC Company")).

    In fact - I'm going to post this as a new feature suggestion I think πŸ˜„

  • @chapman just write a CASE statement.

    SUM(SUM(case when company = 'abc' then `totalSales` end ) FIXED (FILTER DENY company))

    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"
  • @jaeW_at_Onyx fair point!!

  • bradyprice19
    bradyprice19 Member
    edited July 2023

    @jaeW_at_Onyx , I respectfully disagree. Fixed functions helped me solve this really big problem for me:

    While I agree with your statement that an average of an average is bad math, I would say that an average of a sum is GOOD math. This is a normal thing to want which is to go from one granularity and use a group by to get a different granularity where you sum up a metric that you care about and then take an average afterwards to get a more correct value (since an average will be different depending on its granularity).

    This was something that ONLY a fixed function could do and a window function could NOT do since your window function has to contain all the original rows from the dataset and a fixed function does not. Therefore, window functions are flexible to different granularities which prove it more valuable in some cases than window functions.