Error in Data diff function

Vandana
Vandana Member
in Magic ETL

I wanted to find avg daily registrations rate for which the formula will be: on 11th March 2022, we take registration count from Jan 1st to march 11 2022/70. 70 is the which is the total number of days till date.

Formula I used:

(CASE When `Year` = CONCAT(YEAR(CURRENT_DATE())) 

 then COUNT(`Email`) end) / 

 DATEDIFF(CURRENT_DATE(),STR_TO_DATE('2021-01-01','%Y-%m-%d'))


There is no error shown, and formula is valid, still it is not working

Any help is highly appreciated.

Thank you

NO ERROR SHOWN.png


Answers

  • GrantSmith
    GrantSmith Coach

    Since you're only looking at the number of days in the current year have you tried using DAYOFYEAR(CURRENT_DATE()) as your denominator (I'm assuming this is the case since you mentioned 70 days even though you have 2021 as the year)?

    I'd recommend breaking down each component of your beast mode into it's own beast mode and using a data table to display the values. Break out your numerator and denominator. This is helpful to possibly identify any portion which would be causing your error.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • Vandana
    Vandana Member

    Hi Grant, I found the issue..

    Thanks :)

Categories