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

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

Best Answer

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

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

    CASE WHEN `Start Date` = '2050-01-01' THEN 0 ELSE
DATEDIFF(DATE(`Payment Processing Month`),DATE(`Start Date`)
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:

    CASE WHEN `Start Date` = '2050-01-01' THEN 0 ELSE
PERIOD_DIFF(DATE_FORMAT(`Payment Processing Month`,'%Y%m')DATE_FORMAT(`Start Date`,'%Y%m')
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

  • MarkSnodgrass
    MarkSnodgrass 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.
  • AeyaiSaengkeo
    AeyaiSaengkeo 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'

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

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

    CASE WHEN `Start Date` = '2050-01-01' THEN 0 ELSE
DATEDIFF(DATE(`Payment Processing Month`),DATE(`Start Date`)
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:

    CASE WHEN `Start Date` = '2050-01-01' THEN 0 ELSE
PERIOD_DIFF(DATE_FORMAT(`Payment Processing Month`,'%Y%m')DATE_FORMAT(`Start Date`,'%Y%m')
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.
  • AeyaiSaengkeo
    AeyaiSaengkeo Member

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

  • MarkSnodgrass
    MarkSnodgrass 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.

Categories