Skip to content

    • Beast Mode

    Beast Mode

    Can you subtract days based on case when statements?

    Member
    in Beast Mode

    Hello!

    I have a case when statement that I need to subtract a certain amount of days based off of:

    Case

    when 'ID' = '1066' THEN `Maturity Date` - 45

    when 'ID' = '1019' THEN `Maturity Date` - 55

    when 'ID' = '2600' THEN `Maturity Date` - 66

    when 'ID' = '2507' THEN `Maturity Date` - 70

    when 'ID' = '1075' THEN `Maturity Date` - 75

    when 'ID' = '2669' THEN `Maturity Date` - 85

    ELSE `Maturity Date` - 60

    END


    This comes back as an error as it's trying to have the output be a floating decimal instead of a date.

    Any assistance is greatly appreciated. (I'm still fairly new to using Domo)

    Tagged:

    Best Answer

    • Coach
      Answer ✓

      You will want to use the DATE_SUB function to subtract days from a given date. It would look like this:

      1. Case
      2. when 'ID' = '1066' THEN DATE_SUB(`Maturity Date`, INTERVAL 45 day)
      3. when 'ID' = '1019' THEN DATE_SUB(`Maturity Date`, INTERVAL 55 day)
      4. when 'ID' = '2600' THEN DATE_SUB(`Maturity Date`, INTERVAL 66 day)
      5. when 'ID' = '2507' THEN DATE_SUB(`Maturity Date`, INTERVAL 70 day)
      6. when 'ID' = '1075' THEN DATE_SUB(`Maturity Date`, INTERVAL 75 day)
      7. when 'ID' = '2669' THEN DATE_SUB(`Maturity Date`, INTERVAL 85 day)
      8. ELSE DATE_SUB(`Maturity Date`, INTERVAL 66 day)
      9. END

      As far as returning a floating decimal instead of a date, I'm not sure what you would expect to be returned when you are wanting to subtract a number of days from a 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.

    Answers

    • Coach
      Answer ✓

      You will want to use the DATE_SUB function to subtract days from a given date. It would look like this:

      1. Case
      2. when 'ID' = '1066' THEN DATE_SUB(`Maturity Date`, INTERVAL 45 day)
      3. when 'ID' = '1019' THEN DATE_SUB(`Maturity Date`, INTERVAL 55 day)
      4. when 'ID' = '2600' THEN DATE_SUB(`Maturity Date`, INTERVAL 66 day)
      5. when 'ID' = '2507' THEN DATE_SUB(`Maturity Date`, INTERVAL 70 day)
      6. when 'ID' = '1075' THEN DATE_SUB(`Maturity Date`, INTERVAL 75 day)
      7. when 'ID' = '2669' THEN DATE_SUB(`Maturity Date`, INTERVAL 85 day)
      8. ELSE DATE_SUB(`Maturity Date`, INTERVAL 66 day)
      9. END

      As far as returning a floating decimal instead of a date, I'm not sure what you would expect to be returned when you are wanting to subtract a number of days from a 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.

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Quick Links

    Categories