Averaging a date value

user02674
user02674 Member
in Magic ETL

In our dataset we have a driving date of greg_d, but also have other date values for things like sales start and end. These values can change based on program changes and in order to see these changes I wanted to calculate an average sales start and end date associated with each greg_d. Is this something that is possible?

 

Thanks,

Adam

Best Answer

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    Hi @user02674 

     

    Domo doesn't handle the average of date objects with a simple AVG function. You'll need to convert it to a unix timestamp (number of seconds since 1-1-1970), grab the average and then convert it back.

     

    DATE(FROM_UNIXTIME(AVG(UNIX_TIMESTAMP(`start_date`))))

     

    This is assuming your data is recording a record for each change of start / end date for the greg_d dates.

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

Answers

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    Hi @user02674 

     

    Domo doesn't handle the average of date objects with a simple AVG function. You'll need to convert it to a unix timestamp (number of seconds since 1-1-1970), grab the average and then convert it back.

     

    DATE(FROM_UNIXTIME(AVG(UNIX_TIMESTAMP(`start_date`))))

     

    This is assuming your data is recording a record for each change of start / end date for the greg_d dates.

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

Categories