Skip to content

    • Magic ETL

    Magic ETL

    Hi, I'm getting an error doing DateDiff bw two date fields where Nulls exist in one data

    Member
    in Magic ETL
    image.png

    I believe my error popped up when I was trying to complete top two formulas. The Start Date field has some null values and where they're null, we're putting an arbitrary future date in so we can do a calc bw the Start Date and Payment Processing Month field. Thank you in advance.

    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

    Best Answer

    • Coach
      Answer ✓

      I was suggesting re-writing total months on SaaS to this:

      1. CASE WHEN `Start Date` = '2050-01-01' THEN 0 ELSE
      2. DATEDIFF(DATE(`Payment Processing Month`),DATE(`Start Date`)
      3. END

      However, since you are wanting the total months and not the number of days, you will need to do it slightly different. You can use the Period_Diff function to calculate the month difference like this:

      1. CASE WHEN `Start Date` = '2050-01-01' THEN 0 ELSE
      2. PERIOD_DIFF(DATE_FORMAT(`Payment Processing Month`,'%Y%m')DATE_FORMAT(`Start Date`,'%Y%m')
      3. END

      Hope this helps.

      **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

      I believe your error is coming using the date_format inside the datediff. Your format that you are changing it to is not a valid date date format. Is Payment Processing Month not an actual date? I would try wrapping the date function around payment processing month in your 2nd formula as well as start date and see if the error goes away.

      **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.
    • Member

      Thanks for responding Mark. The Payment Processing Month is an actual date. Essentially what we're doing is putting the dates e.g., 2018-02-18 in both columns into Year-Month format so we can calculate the difference in whole months. Does that help? Thanks for the help!

      Also if I tried your suggestion are you saying the date function as in the Date_Format function? I've pasted the formulas below

      TOTAL months on SaaS: CASE WHEN `Start Date` = '2050-01-01' THEN 0 ELSE

      DATEDIFF(DATE_FORMAT(`Payment Processing Month`,'%y%m'),DATE_FORMAT(`Start Date`,'%y%m'))


      Saas Start and is 90 days bw Pmt stmt:

      CASE WHEN DATEDIFF(`First Statement Date`,`Start Date`) <= 90 THEN 'Within 90' ELSE

      'Greater > 90 Days'

    • Coach
      Answer ✓

      I was suggesting re-writing total months on SaaS to this:

      1. CASE WHEN `Start Date` = '2050-01-01' THEN 0 ELSE
      2. DATEDIFF(DATE(`Payment Processing Month`),DATE(`Start Date`)
      3. END

      However, since you are wanting the total months and not the number of days, you will need to do it slightly different. You can use the Period_Diff function to calculate the month difference like this:

      1. CASE WHEN `Start Date` = '2050-01-01' THEN 0 ELSE
      2. PERIOD_DIFF(DATE_FORMAT(`Payment Processing Month`,'%Y%m')DATE_FORMAT(`Start Date`,'%Y%m')
      3. END

      Hope this helps.

      **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.
    • Member

      Thank you Mark. Using the PeriodDiff did it for me. Thank you so much.

    • Coach

      @AeyaiSaengkeo that's great to hear! If you can accept any answers that helped you, that will help others in the community.

      **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