Min/Max of date

We currently have a problem where we need our beastmode calculation to take the first and last date in a given range based on what the analyzer is set to and what the date granualarity is set to - we soon realized that min/max does not work in the context of dates.

 

The ability for a intelligently apply aggregations to dates would be particularly useful for calculating things like growth rate, variances during a period, etc.

3
3 votes

· Last Updated

Comments

  • I agree, and using other methods (such as boolean logic) to find that max or minimum dates does not function properly because of how the background code of DOMO appears to function.

     

    For instance the code:

     

    CASE WHEN '6/1/2016' > '5/1/2017' THEN 1 ELSE 0 END

     

    Returns 1, not 0.  Presumably it is testing the month but not month and year.

     

    We desperately need a MAXDATE or MINDATE type calculation. While this can accomplished using a SQL dataflow having this ability in Beastmode would help in a variety of circumstnces.

     

     

  • @mbelmont - I hadn't noticed this... time to go double check every beastmode calculation in use at our company...

     

    A solution we've used in the past to overcome similar issues is to just add a column of dates converted to integers in the MySQL dataflow - but this is messy and opens the door for big mistakes.

  • @anafziger - Uh oh...don't shoot the messenger...haha.

     

    And I agree about the SQL dataflow.  I would rather this be handled as a beastmode function.

     

    Devs?

  • @mbelmont In your example above, I wonder if beast mode is interpreting your dates as strings.  In that case, 6 does come after 5 so it should return a 1.  If you throw a STR_TO_DATE function around that, do you get the same result?

     

    Under certain circumstances, MIN(`Date`) and MAX(`Date`) will work where `Date` is a date datatype.  Like looking at oldest and newest transactions per customer.  Or even per customer per month (for customer X, month of May, the first transaction was on the 2nd and the last was on the 28th).  

     

    I'm sure the devil's in the details.

     

     

    Aaron
    MajorDomo @ Merit Medical

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

    domo

    💎

    Thank you for submitting this @anafziger. I am assigning to our product manager @ckwright to review and comment.

  • Thanks @btm!

  • @AS, yes you are correct the result changes when I add that into the calculation.  However, I have some rather complicated code that is still not firing correctly when I am comparing dates even after adding that into the logic.  I am still trying to find the pattern and I am not going to post the code because it is absurdly long, but I think a beast mode that both defines date format for the calculation and also can find the max or min date is crucial.

     

     

  • I have a great example for this use case:

     

    I want to calculate compliance as defined by submission days within a chosen date range. This calculation is as simple as:

     

    # of days with a submission/# of days in the card's (currently selected) date range

     

    I can't do this unless I can call to the selected date range and return the min and max dates!

     

    If I hardcode a date range in it works but then I can't change the date range without changing the calculation. If I tell it to use the earliest submission as the anchor and curdate() as the last day it loses the ability to be expanded to a range that doesn't include current.

     

    This is a big limitation.

  • Absolutely need a MaxDate formula. Is there an updated to this?

This discussion has been closed.